Count the number of values that satisfy criteria in two separate columns but only count the first example of each paired criteria

[Fitzy22]

Hi,

Really hoping someone can help me here, any assistance will be thoroughly appreciated!!

I am trying to write a formula that will count the number of values greater than 0 occurring in one column of an Excel spreadsheet, but only the first instance of each category which is in a second column.

The image shows four values greater than zero (circled). One for Acarina at the top and three for Coleoptera further down. The count that I want from this dataset would be two as I don't need to count the Coleoptera duplicates.

Note that I don’t need to sum the values in the right column, it's a count only.

I hope I have explained myself adequately and really keen to provide further information if needed.

Many thanks, Anton.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=ROWS(UNIQUE(FILTER(B2:B1000,G2:G1000>0)))

 

[Fitzy22]

Hi & welcome to MrExcel.
How about

=ROWS(UNIQUE(FILTER(B2:B1000,G2:G1000>0)))

Absolutely perfect, thanks Fluff!!

I had been messing around with every convoluted equation imaginable and this was just brilliant.

Cheers mate.

 

[Fluff]

You're welcome & thanks for the feedback.