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