CODEWITHSUNDEEP
excel-formula
jwk2011
jwk2011

Reputation: 23

Using sumproduct and countifs to find unique values that match a criteria

I have one column ($C$13:$C$78) with UserID's some of which are duplicates and another column ($K$13:$K$78) with "TicMarks" and a criteria in ($A$7). I'm trying to count the number of unique UserID's that meet the specified criteria.

I have already tried using a combination of sumproduct and countifs, but the countifs array returns zeros for values that dont meet the criteria and then i try to take the array and divide by zero to return only unique ID's, but obviously return #DIV/0!.

'current formula

=SUMPRODUCT(1/COUNTIFS($K$13:$K$78,$A$7,$C$13:$C$78,$C$13:$C$78))

'formula after looking at countifs specifically

=SUMPRODUCT(1/{0;0;0;0;0;0;0;0;2;2;1;1;1;0;1;1;1;1;1;1;1;3;3;3;3;1;1;1;1;3;3;3;3;2;2;2;2;2;2;1;0;0;0;0;0;0;0;0;0;0;0;0;1;1;1;0;0;0;0;1;2;2;1;1;1;1})

Expected results are to return the unique number of userid's that meet a specified criteria. Is there a way to ignore the returned 0's and only divide by >0?

Upvotes: 1

Views: 941

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

you need 0 out the numerator when it does not meet the criteria and deal with the #DIV/0 error:

=SUMPRODUCT(($K$13:$K$78=$A$7)/(COUNTIFS($K$13:$K$78,$A$7,$C$13:$C$78,$C$13:$C$78)+($K$13:$K$78<>$A$7))

Upvotes: 2

Related Questions