Count unique data in Column, but exclude if certain data is in another Column

[dgroff]

I have a list of names in column B with some repeating. I use the following formula to count how many unique names are in the column.

=ArrayFormula(SUM(IFERROR(1/COUNTIF(B5:B,B5:B),0)))

I now want to reference data in column C, which are numbers, and only count the unique names in Column B if the correlating number in Column C is greater than 0.


Your help is appreciated.

[hoyasaxa215]

Is =ArrayFormula a UDF?

[Tony Valko]

Post some sample data and tell us what result you expect.

[dgroff]

Here is a sample workbook:Book.xlsx

[martindwilson]

=ArrayFormula(SUM(IFERROR(1/COUNTIF(B5:B,B5:B),0))) is google sheets syntax

[Vikas_Gautam]

here is the solution..

Please Login or Register  to view this content.

Edit:- Tony's solution is more efficient... should go with that..

check the attached file..

[Tony Valko]

The note in the file says:

Count Unique Cities in Column B but exclude if Column C is more then 0 (this exmple would be 7)

However, I don't see how it's possible to arrive at that result.

Did you mean:

Count Unique Cities in Column B where column C is more than 0 (this exmple would be 7)

If that's the case try this array formula**:

=SUM(IF(FREQUENCY(IF(C2:C19>0,MATCH(B2:B19,B2:B19,0)),ROW(B2:B19)-ROW(B2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[dgroff]

Apologies, that's what I meant.

Thank you Tony Valko & Vikas Gautam, they both worked! Exactly what I needed!

[Tony Valko]

You're welcome. We appreciate the feedback!

[dgroff]

1 Step further... What if I added another column to this?

So "Count Unique Cities in Column B where Column C and/or Column D are more than 0"

[Vikas_Gautam]

The revised formula would be:-
for AND Condition:

Please Login or Register  to view this content.

For OR Condition:

Please Login or Register  to view this content.

[dgroff]

Thank you!