Excel Formula to Count Unique Values that Meet Multiple Criteria

0
votes

I have a table that counts total occurrences that meet multiple criteria, now I need another table that counts unique values based on the same criteria.

I have been playing with sumproduct and frequency but haven't gotten anything to work.

this is the base formula for the original table:

=COUNTIFS('UC DB'!$I:$I,">="&$B3,'UC DB'!$I:$I,"<"&$B4,'UC 
DB'!$L:$L,"TRUE",'UC DB'!$DJ:$DJ,"FALSE")

The column I want to test for unique values is 'UC DB'!$A:$A I've tried:

=SUM(IF(COUNTIFS('UC DB'!A:A,'UC DB'!A:A,'UC DB'!I:I,">="&B3,'UC DB'!I:I," 
<"&B4,'UC DB'!L:L,"TRUE",'UC DB'!DJ:DJ,"FALSE")=1,1,0))

And:

=SUM(1/COUNTIFS('UC DB'!A:A,'UC DB'!A:A,'UC DB'!I:I,">="&B3,'UC DB'!I:I," 
<"&B4,'UC DB'!DJ:DJ,"FALSE"))

and a few others and nothing seems to work.

1
excelexcel-formulauniquecountif
Possible duplicate of Excel - Counting unique values that meet multiple criteria – GalAbra
Have a look at this – JvdV
I have deleted my post because the amendment required would make an already very inefficient formula even more so, and so it wouldn't be practical. – Domenic
What would be a more efficient way then? – Joe

1 Answers

0
votes

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF('UC DB'!$I$2:$I$100>=$B3,IF('UC DB'!$I$2:$I$100<$B4,IF('UC DB'!$L$2:$L$100=TRUE,IF('UC DB'!$DJ$2:$DJ$100=FALSE,IF('UC DB'!$A$2:$A$100<>"",MATCH('UC DB'!$A$2:$A$100,'UC DB'!$A$2:$A$100,0)))))),ROW('UC DB'!$A$2:$A$100)-ROW('UC DB'!$A$2)+1)>0,1))

Adjust the range accordingly. However, it should be more efficient to use a helper column. For example, let's choose Column DK as our helper column. First, enter the following formula in DK2, and copy the formula down the column...

=IF('UC DB'!I2>=Sheet2!$B$3,IF('UC DB'!I2<Sheet2!$B$4,IF('UC DB'!L2=TRUE,IF('UC DB'!DJ2=FALSE,A2,""),""),""),"")

Then, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF('UC DB'!DK2:DK100<>"",MATCH('UC DB'!DK2:DK100,'UC DB'!DK2:DK100,0)),ROW('UC DB'!DK2:DK100)-ROW('UC DB'!DK2)+1)>0,1))

Adjust the range accordingly.

Hope this helps!