Another question on count formula

[David Lipetz]

In a large spreasheet, I want to be able to count the number of rows where
column O is a nonblank (has some type of data entered - may be alpha or may
be a date) AND column P = "Y".

Can someone please point me in the right direction in terms of which formula
to use?

Thanks,
David

 

[Bobocat]

=sumproduct((isblank(o)=false)*(p="Y")

 

[David Lipetz]

This formula =SUMPRODUCT((ISBLANK(O2:O425)=FALSE)*(P2425="Y")) does not
work correctly. The result should be 31 but the formula results in 66 which
is simply the number of rows in P that =Y without regards to O.

Ideas?

 

[Bobocat]

are you sure that the cell in column O is blank?
use Isblank(cell) to check? true = blank, false =not blank?

 

[Bobocat]

OR select o2425, press F5 -> special -> blank
to check whether the "blank cell" is blank

 

[Bob Phillips]

=SUMPRODUCT(--(LEN(O2:O425)>0),--(P2425="Y"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)