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!!

[TABLE="width: 470"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Closed[/TD]
[TD]Name[/TD]
[TD]1/COUNTIF($B$2:$B$9,$B$2:$B$9)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]Test[/TD]
[TD="align: right"]0.333333333[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD]Test1[/TD]
[TD="align: right"]0.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]Test[/TD]
[TD="align: right"]0.333333333[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]Test2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]Test1[/TD]
[TD="align: right"]0.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD]Test[/TD]
[TD="align: right"]0.333333333[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]Test1[/TD]
[TD="align: right"]0.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]Test1[/TD]
[TD="align: right"]0.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]SUMPRODUCT(1/COUNTIF($B$2:$B$9,$B$2:$B$9))[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="colspan: 3"]SUMPRODUCT(1/COUNTIFS($A$2:$A$9,"No",$B$2:$B$9,$B$2:$B$9))[/TD]
[TD="align: right"]3.83333[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!