概要

SUMPRODUCT関数を使って、空白と重複を除いた件数を条件付きでカウントする数式を紹介します。

例として、以下のExcelではF列に都道府県が空白と重複込みで入力されています。
ここから、空白と重複を除き、かつ、「一番右の文字が"県"」の条件に一致するデータの件数をカウントする数式を作ります。

数式

条件付き

=SUMPRODUCT(((F3:F17<>"")/COUNTIFS(F3:F17,F3:F17&""))*(RIGHT(F3:F17,1)="県"))

上のExcelで黄色いセルに入力されている数式がこちらです。
青色の部分がカウントする範囲です。4か所ありますが全て同じ範囲を指定してください。
(RIGHT(F3:F17,1)="県")の部分がカウント条件です。こちらも必要に応じて変更してください。
例えば、3文字の都道府県を集計したければ(LEN(F3:F17)=3)に変える等です。

条件無し

=SUMPRODUCT((F3:F17<>"")/COUNTIFS(F3:F17,F3:F17&""))

条件が必要なく、単に空白と重複を除いた件数をカウントしたい場合はこちらの数式になります。

複数条件

=SUMPRODUCT(((F3:F17<>"")/COUNTIFS(F3:F17,F3:F17&""))*(RIGHT(F3:F17,1)="県")*(LEN(F3:F17)=3))

複数の条件を付ける場合は*を使って横に並べていきます。

解説

「数式の検証」を使って流れを追ってみましょう。
わかりやすいよう、F3～F8の範囲までデータを減らしています。
数式の条件は同じで、空白と重複を除き、かつ、「一番右の文字が"県"」の条件に一致するデータの件数をカウントします。

Step 1

数式の検証を開始しました。
計算の途中結果を表形式で書いていきます。

データ
東京都
千葉県
（空白）
千葉県
神奈川県
東京都

Step 2

まずF3:F8<>""の部分が計算されました。
空白でなければTrue、空白ならFalseが入ります。

データ	<>""
東京都	True
千葉県	True
（空白）	False
千葉県	True
神奈川県	True
東京都	True

Step 3

次にCOUNTIFSの2つ目の引数、検索条件部分のF3:F8&""が計算されます。
空白（何も入力されていないセル）の数をカウントする必要があるため、範囲の後ろに&""を付けています。

Step 4

F3:F8&""が計算されました。これでCOUNTIFSの式が以下の表のように出来上がった形になります。

データ	<>""	COUNTIFS
東京都	True	F3～F8から"東京都"の数をカウントする
千葉県	True	F3～F8から"千葉県"の数をカウントする
（空白）	False	F3～F8から""の数をカウントする
千葉県	True	F3～F8から"千葉県"の数をカウントする
神奈川県	True	F3～F8から"神奈川県"の数をカウントする
東京都	True	F3～F8から"東京都"の数をカウントする

Step 5

COUNTIFS(F3:F8,F3:F8&"")が計算されました。結果は以下の表の通りです。

データ	<>""	COUNTIFSの結果
東京都	True	2
千葉県	True	2
（空白）	False	1
千葉県	True	2
神奈川県	True	1
東京都	True	2

Step 6

F3:F8<>""の結果をCOUNTIFS(F3:F8,F3:F8&"")の結果で除算します。
Trueは1、Falseは0として扱われますので、結果は以下の表の通りになります。

空白のセルは<>""の結果が0になるため、除算の結果も0になりカウントから除外されるという仕組みになります。
また、COUNTIFSの条件に&""を付けていないと、空白の検索結果が0になり、#DIV/0!のエラーが発生します。

データ	<>""	COUNTIFSの結果	<>"" ÷ COUNTIFSの結果
東京都	True	2	0.5
千葉県	True	2	0.5
（空白）	False	1	0
千葉県	True	2	0.5
神奈川県	True	1	1
東京都	True	2	0.5

Step 7

続いてRIGHT(F3:F8,1)が計算されます。

Step 8

RIGHT(F3:F8,1)が計算されました。結果は以下の表の通りです。
右端から数えて1文字が表示されています。

データ	<>"" ÷ COUNTIFSの結果	RIGHTの結果
東京都	0.5	"都"
千葉県	0.5	"県"
（空白）	0	""
千葉県	0.5	"県"
神奈川県	1	"県"
東京都	0.5	"都"

Step 9

続いてRIGHT(F3:F8,1)="県"が計算されました。
右端から数えて1文字が"県"ならTrue、違ければFalseが入ります。

データ	<>"" ÷ COUNTIFSの結果	RIGHTの結果	RIGHTの結果 = "県"
東京都	0.5	"都"	False
千葉県	0.5	"県"	True
（空白）	0	""	False
千葉県	0.5	"県"	True
神奈川県	1	"県"	True
東京都	0.5	"都"	False

Step 10

大詰めです。「<>"" ÷ COUNTIFSの結果」と「RIGHTの結果 = "県"」を乗算します。

データ	<>"" ÷ COUNTIFSの結果	RIGHTの結果 = "県"
東京都	0.5	False
千葉県	0.5	True
（空白）	0	False
千葉県	0.5	True
神奈川県	1	True
東京都	0.5	False

Step 11

「<>"" ÷ COUNTIFSの結果」×「RIGHTの結果 = "県"」の計算結果は以下の通りになりました。
前述の通り、Trueは1、Falseは0になるため、「RIGHTの結果 = "県"」の条件に一致しないデータはカウントから除外されるという仕組みです。

データ	<>"" ÷ COUNTIFSの結果	RIGHTの結果 = "県"	最終結果
東京都	0.5	False	0
千葉県	0.5	True	0.5
（空白）	0	False	0
千葉県	0.5	True	0.5
神奈川県	1	True	1
東京都	0.5	False	0

Step 12

最後に、SUMPRODUCTで今まで計算した値が合計されます。
0 + 0.5 + 0 + 0.5 + 1 + 0 = 2となり、これで空白と重複を除き、かつ、「一番右の文字が"県"」の条件に一致するデータの件数をカウントできました。

データ	最終結果
東京都	0
千葉県	0.5
（空白）	0
千葉県	0.5
神奈川県	1
東京都	0

合計	2

動作環境

Microsoft Excel 2013
※執筆時は2013で検証しましたが、Microsoft 365のExcelでも同様に動作することを確認しています。