[[20220711134526]]　『SUBTOTALとSUMIF関数を組み合わせたい』（ジーンズ）　ページの最後に飛ぶ

『SUBTOTALとSUMIF関数を組み合わせたい』（ジーンズ）

お世話になります。
元々は以下の表で末尾Aを判定させずに合計数を求めるという関数を組んでいました。

　　［A］［B］
［1］10 1
［2］11 2
［3］12 3
［4］1A -1
［5］　 5(合計数）

 =SUMIF(A1:A4,"<>*A",B1:B4)

ここに西暦を加え、それをフィルター機能で絞り込んだものを合計させたいのですが、SUBTOTALとSUMIF関数は併用が不可とのことで代替えの関数を調べていましたが、立ち行かなくなりました。

　　［A］［B］［C］
［1］10 2020年　1
［2］11 2021年　2
［3］12 2022年　3
［4］1A 2022年　-1
［5］　　　　　5(合計数）

A4の末尾がAなのでC4の-1は判定させず、B列をフィルター機能で2022年に絞り込んでC5の合計数を3にするという関数を求めています。（伝わりますか？）

=SUMPRODUCT((SUBTOTAL(3,INDIRECT("<>*A"&ROW($A$1:$A$4))))*($A$1:$A$4="<>*A")*(C1:C4))

調べつつ繋ぎ合わせて上記の関数を組んでみましたが、キメラみたいになってしまいました…。
どうかお知恵を拝借させてください。

＜使用 Excel：Office365、使用 OS：Windows10 ＞

 フィルター使うのであれば1行目が見出しでデータは2行目から5行目だとして。
 =SUMPRODUCT(SUBTOTAL(3,INDIRECT("A"&ROW(2:10)))*(RIGHT(A2:A10)<>"A")*C2:C10)
 ではどうだろうか？
（ねむねむ） 2022/07/11(月) 14:12

 フィルター機能を使うのであればB列を年度で絞込、A列をAで終わらないで絞り込むという方法をあるのでは？
（ねむねむ） 2022/07/11(月) 14:16

 365なら SUMPRODUCT → SUM でもいいかも
 というか FILTER 関数でもできるかも･･･

 以上 、参考まで
（笑） 2022/07/11(月) 14:20

=SUMIF(C1:C4,A1:A4,"<>*A",B1:B4,2022)

では何が問題なんでしょう。フィルタの条件をどこかに書き出しておいて
それを参照するだけで良さそうに思いますが。
（d-q-t-p） 2022/07/11(月) 14:23

 ＞=SUMIF(C1:C4,A1:A4,"<>*A",B1:B4,2022)
 ＞では何が問題なんでしょう。

 SUMIF → SUMIFS では？

 以上
（笑） 2022/07/11(月) 14:28

 もしかしたら複数の年度を選択する可能性もすこしはあるかも。
（ねむねむ） 2022/07/11(月) 14:34

たくさんのご意見ありがとうございます。
ねむねむ様から頂戴した

=SUMPRODUCT(SUBTOTAL(3,INDIRECT("A"&ROW(2:10)))*(RIGHT(A2:A10)<>"A")*C2:C10)

上記の関数で思い通りの結果が得られました。
フィルター機能で絞り込むという手段は出来るだけ関数でどうにかしたいと思っていましたので、おかげ様でなんとかなりました。
d-q-t-p様もご提案頂きありがとうございました。
またご相談させて頂くかもしれませんが、よろしくお願い申し上げます。
（ジーンズ） 2022/07/11(月) 14:46

 ＞フィルター機能で絞り込むという手段は出来るだけ関数でどうにかしたい
 ちょっと意味がわかりませんね。
 数式で抽出したかったんですか？

 F2セルに条件の年を入力
 G～I列に抽出

 G2セルに　=FILTER(A2:C10,B2:B10=E2,"")　と入れるだけで他のセルにも表示されませんか？

 A列が「A」で終わるものは除外して抽出したいのなら
 G2 =FILTER(A2:C10,(B2:B10=E2)*(RIGHT(A2:A10)<>"A"),"")

 実際の表は何行あるのか知りませんけど･･･

 以上、参考まで
（笑） 2022/07/11(月) 15:01

コメント返信：

[ 一覧(最新更新順) ]

[[20220711134526]] 『SUBTOTALとSUMIF関数を組み合わせたい』（ジーンズ） ページの最後に飛ぶ

[[20220711134526]]　『SUBTOTALとSUMIF関数を組み合わせたい』（ジーンズ）　ページの最後に飛ぶ