Unique count based on two criteria

[MichelleC987]

Good Morning,

I'm looking for some assistance on a formula that will provide a count of unique instances in column B, when Column A is "No". I have success with (=SUMPRODUCT(1/COUNTIF($B$2:$B$9,$B$2:$B$9))), but all is lost when I try to change the COUNTIF to a COUNTIFS to encompass the additional criteria as illustrated below.

This workbook will be used by many users with limited excel background; therefore I would prefer to not include an array formula that would require (Ctrl+Shift+Enter) to calculate. Due to a limitation by the excel file type itself, I cannot apply VBA to this workbook. Any help would be greatly appreciated. Thank you!!

Closed	Name	1/COUNTIF($B$2:$B$9,$B$2:$B$9)
No	Test	0.333333333
Yes	Test1	0.25
No	Test	0.333333333
No	Test2	1
No	Test1	0.25
YES	Test	0.333333333
No	Test1	0.25
No	Test1	0.25
SUMPRODUCT(1/COUNTIF($B$2:$B$9,$B$2:$B$9))	3
SUMPRODUCT(1/COUNTIFS($A$2:$A$9,"No",$B$2:$B$9,$B$2:$B$9))	3.83333

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[AFPathfinder]

If VBA and arrays are off the table, I think the only solution to get a count of unique values may be the advanced filter. If you use the advanced filter on column A as "No" and include column B and check the "Unique records only" box, that should give you a new table of all the records of "No" with a unique list of values in column B. You can then use a COUNTA formula to count the results.

The other way would be to just copy the filtered table to a different range and run the Remove Duplicates tool, then a COUNTA to pull your count.

 

[MichelleC987]

That worked! I haven't heard of the advanced filter feature before, there are a few other workbooks that can utilize this feature. Thank you for solving the problem and teaching me a cool new tool!

 

[AFPathfinder]

You're welcome!