Thread: unique counting + filter

I have a column that lists ID and I have another column that lists department
I want to count the number of unique IDs but also limit them by department
and I want to do this with a formula . not by filtering
the counting without filtering is ok
=sum(if(frequency(C:C;C:C)>0;1))
where can I put in something like if(D:D="Sales")

Try...

=SUM(IF(FREQUENCY(IF(D2:D100="Sales",C2:C100),IF(D2:D100="Sales",C2:C100))>0,1))

or

=SUM(IF(FREQUENCY(IF(D2:D100="Sales",IF(C2:C100<>"",MATCH("~"&C2:C100,C2:C100&"",0))),ROW(C2:C100)-ROW(C2)+1)>0,1))

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER. Also, note that whole column references cannot be used.

Hope this helps!

it did not work.

the answer should be 7

yes it did work . I did a mistake by not ctrl + **** entering it

thank you for helping out

much appreciated

However, based on the sample data, if the formula should return 7 and not 8, the first formula would need to be changed to allow empty cells in Column C...

=SUM(IF(FREQUENCY(IF(D2:D100="Sales",IF(C2:C100<>"",C2:C100)),IF(D2:D100="Sales",IF(C2:C100<>"",C2:C 100)))>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

thanks, what if I want to do the exact same thing but with sum not unique count ?

Note sure what you mean, can you be specific?

sorry for delayed feedback
I solved it with a simple sumif