How to count with 2 status combined?

Referring to http://www.excelforum.com/excel-gene...ml#post2350875

I tried to filter again by counting only fruits that have status that is not "NIL".

=SUMPRODUCT((B3:B15="Apple")*(C3:C15<>"NIL")/COUNTIF(A3:A15,A3:A15&""))

I get weird numbers in the end. I have attached a sample here. My excel is ver 2000.
I have highlighted red on the questionable cells.

Per the earlier thread - where the relationship between values ceases to be 1:1 then things become more complex.

Here for ex. any given Serial Number can be both NIL and non-NIL meaning you can no longer base the unique count on the instances of the Serial Number alone.

Please Login or Register  to view this content.