COUNT unique values in A:A IF B:B = x

[callummcgrath]

Hey,

I'm after a way to count unique values in a column with a criteria. Please see attached,
Count Unique IF.xlsx

Thanks!

Callum

[AliGW]

Try this:

=SUM(--(FREQUENCY(IF(B2:B1000=21,MATCH(A2:A1000,A2:A1000,0)),ROW(B2:B1000)-ROW(B2)+1)>0))

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Glenn Kennedy]

.... or another possibility: an ordinary formula:

=SUM(INDEX(($B$2:$B$28=21)/COUNTIFS($B$2:$B$28,$B$2:$B$28,$A$2:$A$28,$A$2:$A$28),0))

[Phuocam]

Or:

=SUM(IF(C2:C33=F2,1/COUNTIFS(A2:A33,A2:A33,C2:C33,F2)))

CTRL+SHIFT+ENTER

[callummcgrath]

All 3 of you are amazing, each of them worked perfectly.

Thank you!

[AliGW]

You're welcome!

[Glenn Kennedy]

I should add that mine has one drawback. It needs the ranges to be exact. So, if you go with it... it's advisable either to use a fixed (exact) range or to use a dynamic named range to auto-adjust the ranges chosen by the formulae. Do you know how to set these up? Are the values in columns A & B direct entries, or are they the result of other formulae?