COUNTIFS(カウントイフス)エクセル(Excel)関数

複数列の条件が一致している数を出す方法

複数条件を満たす関数4つ

1,複数列の条件が一致しているセルの個数
2,複数列の条件が一致している合計値
3,複数列の条件が一致している1番大きい数字
4,複数列の条件が一致している1番小さい数字


1
複数列の条件が一致しているセルの個数

通常セルの個数を数えるのは「COUNT」関数
複数条件を指定するのは「IFS」関数
この2つがくっついた「COUNTIFS」という関数を使います
・関数COUNTIFS(カウントイフス)
・書式=COUNTIFS(範囲,検索条件,範囲,検索条件・・・)
・内容複数の検索条件を満たすセルがいくつあるかを求めます。範囲と条件の組み合わせで最大127組指定することができます
エクセル表サンプル
  A B C D E F
1            
2   日時 分類 内容 金額  
3   8月10日 支出 通信費 10,800  
4   8月13日 支出 外食 3,150  
5   8月15日 支出 通信費 6,755  
6   8月15日 支出 外食 4,000  
7   8月20日 支出 定期代 5,500  
8   8月20日 支出 外食 2,100  
9   8月22日 支出 外食 2,100  
10   8月25日 収入 給与 354,570  
11   8月27日 支出 家賃 120,000  
12   8月28日 支出 外食 4,200  
13   8月28日 支出 電気代 12,545  
14   8月28日 支出 外食 2,100  
15            
例)8月28日支出の数
=COUNTIFS(B3:B14,"8月28日",C3:C14,"支出")
結果:3


2
複数列の条件が一致している合計値

合計値を計算する「SUM」関数
複数条件を指定するのは「IFS」関数
この2つがくっついた「SUMIFS」という関数を使います
・関数SUMIFS(サムイフス)
・書式=SUMIFS(合計範囲,検索範囲,条件,検索範囲,条件)
・内容一覧表の中から指定された検索条件に一致するセルの値を合計します。
例)8月28日外食の合計
=SUMIFS(E:E,B:B,"8月28日",D:D,"外食")
結果:6300


3,4
複数列の条件が一致している1番大きい数字と小さい数字

配列から1番大きい数字を出す「MAX」関数、小さい数字を出す「MIN」関数
複数条件を指定するのは「IFS」関数
この2つがくっついた「MAXIFS」「MINIFS」という関数を使います
・関数MAXIFS(マックス イフ エス)
・書式=MAXIFS(最大範囲,条件1範囲, 条件1,条件2範囲,条件2,・・・)
・内容複数の条件で最大値を取得します
・関数MINIFS(ミニマム イフ エス)
・書式=MINIFS(最小範囲,条件1範囲, 条件1,条件2範囲,条件2,・・・)
・内容複数の条件で最小値を取得します
結果として取得する範囲が最大か最小かで使い方は一緒です
=MAXIFS(最大範囲,条件1範囲, 条件1,条件2範囲,条件2,・・・)
=MINIFS(最小範囲,条件1範囲, 条件1,条件2範囲,条件2,・・・)
  A B C D E F
1            
2   名前 学年 チーム 得点  
3   佐藤 3年 赤組 467  
4   鈴木 2年 白組 450  
5   高橋 1年 赤組 438  
6   田中 2年 白組 470  
7   渡辺 3年 白組 465  
8   伊藤 3年 赤組 444  
9   山本 1年 白組 432  
10   中村 1年 赤組 434  
11   小林 2年 赤組 456  
12   加藤 2年 赤組 455  
13   吉田 1年 白組 451  
14   山田 3年 白組 449  
上の表から 3年赤組最高得点を出します
=MAXIFS(最大範囲,条件1範囲, 条件1,条件2範囲,条件2)

=MAXIFS(E3:E14,C3:C14,"3年",D3:D14,"赤組")
結果 467

2年で赤組の最高得点
=MAXIFS(E3:E14,C3:C14,"2年",D3:D14,"赤組")
結果 456

1年で白組の最高得点
=MAXIFS(E3:E14,C3:C14,"1年",D3:D14,"白組")
結果 451

条件1範囲, 条件1は条件126範囲, 条件126と126セットまで指定できます


結果が0になる、見つけてくれない場合

検索条件は完全一致のセルでないと呼び出してくれません

範囲か検索条件が"支出"でなく
"支 出" ← 文字の間に半角スペース
"支出 " ← 文字の後ろに半角スペース
" 支出" ← 文字の前に半角スペース
半角スペースが1つあるだけでも一致したセルにはなりません

検索条件の数式を間違えることはあまりないと思われるので、範囲の文字列に半角スペースなどが含まれていないか、含まれていたら検索条件に指定した文字以外は削除します


結果が0になる、日時を呼び出している場合

2通りが考えられます

=COUNTIFS(B3:B14,"8月28日",C3:C14,"支出")

1つ目:範囲が文字列で入力されている場合
検索条件は「8月28日」を探しているわけではなく
「8月28日」のシリアル値を検索します
8月28日 表示が日時の場合:「8月28日」
検索しているシリアル値:「43705 」
したがって範囲が文字列で「8月28日」と入力されている場合
「8月28日」という数字と漢字なので一致しません
対応策:範囲の表示形式を日付の8月28日を選択して日付で打ち直します

2つ目:範囲の「8月28日」が今年ではない場合
何かの表や去年から使いまわして日付をコピーした場合
「8月28日」が実は「2017年8月28日」など今年ではない場合
検索条件の「8月28日」は今年なので一致しません(条件の年号を省略すると今年になります)
対応策:セルをクリックして見ると年号も表示されるので今年に直します

また検索している関数がTODAYやNOWの場合
TODAYもNOWも表示を日付にすれば「10月21日」ですが
シリアル値が違うので一致しません
=NOW()  43759.590659722
=TODAY() 43759
NOW()は小数点以下に時間を表示しています
今日を検索するにはNOW()ではなくTODAY()を使います


COUNTIFSとよく使うエクセル関数
 SUM  指定したセル範囲の合計
 SUMIF  検索条件に一致するセルの値を合計
 VALUE  数値の文字列を数値に変換
HOME

エクセル(Excel)関数

(C) 2001-2019 Digital World