Maximum with criteria and blanks

[Oxidised]

Hi, I have a data set that is just random numbers in column A between 0-99, but there are also some blanks because there were no readings from the raw data when it was taken. But these are not true blanks, they are like a "" so read as text?

In column B I have criteria "A", "B", or "C"

To find the maximum value for "A" I would normally write

=sumproduct(max((A2:A100)*(B2:B100="A")))

But due to the "blanks" in column A, it throws a #value error. How can I filter out these errors and still get the correct answer?

(note - I could clean up the data with a new column to make any non-number cells = 0, but I have 188,000 rows of data and I dont really want to add another column - prefer to do it in the formula if possible)

Thanks, Oxi

 

[Nebu]

Guess this is what u are looking for....

 

[Oxidised]

Ah yeah, pivot table works... I never remember about pivot tables, thanks!

 

[Somendra Misra]

Hi Oxidised,

Here is formula also :

In E2 and copy down:

=LARGE(IF((D2=$B$2:$B$100)*ISNUMBER($A$2:$A$100),$A$2:$A$100),1)

Enter with Ctrl+Shift+Enter.

Regards,