0

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.

Joe
  • 3
  • 3
  • 1
    Possible duplicate of [Excel - Counting unique values that meet multiple criteria](https://stackoverflow.com/questions/5092902/excel-counting-unique-values-that-meet-multiple-criteria) – GalAbra Jun 19 '19 at 19:38
  • Have a look at [this](https://stackoverflow.com/questions/56059103/not-able-to-find-unique-values-in-excel/56059438#56059438) – JvdV Jun 19 '19 at 20:17
  • 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 Jun 20 '19 at 16:10
  • What would be a more efficient way then? – Joe Jun 21 '19 at 18:02

1 Answers1

0

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!

Domenic
  • 7,844
  • 2
  • 9
  • 17