Count unique number of string based on condition in excel

Question

I want to count unique strings based on condition in excel.

In the example below, column B to F are inputs and columns G and H is expected output.

Currently I am using formula for H6 =COUNTIFS(E1:E20, "<>", B1:B20, G6, C1:C20, "v1", D1:D20, "f") But with this formula 1 is counted 2 times, but I don't want to count duplicate entries.

How can I ignore duplicates and count only distinct values based on condition?

Answer 1

In H6, array formula (Ctrl+Shift+Enter) copied down :

=SUM(N(FREQUENCY(IF(($B$1:$B$20=G6)*($C$1:$C$20="V1")*($D$1:$D$20="f"),$E$1:$E$20),$E$1:$E$20)>0))

Answer 2

If you have Excel 2016+ with the UNIQUE function, you can use:

=SUMPRODUCT((INDEX(UNIQUE(input_arr),,1)=G6)*(INDEX(UNIQUE(input_arr),,2)="v1")*(INDEX(UNIQUE(input_arr),,3)="f")*(INDEX(UNIQUE(input_arr),,4)<>""))

where input_arr refers to =$B$1:$E$20

If you have an earlier version of Excel, I would suggest

• Copy original data to a new location
• Select the entire table, then "Remove Duplicates" ensuring all columns are selected
• If new table is, for example, in B37:E37, use the formula:

---

=SUMPRODUCT((INDEX($B$27:$E$37,,1)=G6)*(INDEX($B$27:$E$37,,2)="v1")*(INDEX($B$27:$E$37,,3)="f")*(INDEX($B$27:$E$37,,4)<>""))