Count based on values from 2 columns (nested COUNTIF)

[Joann]

I want to count instances where column T CONTAINS the letter "A" AND
where column L is not blank. If I add an extra column which I then
count, it works, but it sure is clunky:
=IF((ISERROR(SEARCH("A",T2))),"",IF(ISBLANK(L2),"-","A"))
I hope someone will lead me to a more elegant solution. Thanks,
Joann

 

[Peo Sjoblom]

=SUMPRODUCT(--(L1:L1000<>""),--(ISNUMBER(SEARCH("A",T1:T1000))))

or

=SUMPRODUCT(--(LEN(L1:L1000)=0),--(ISNUMBER(SEARCH("A",T1:T1000))))

if you want case sensitive
change search to find




--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Joann]

Thank you! Works fine; one question, tho: what is the "--" operator?
The formula doesn't work without them, but I can't find a ref. in
HELP. Joann

 

[JE McGimpsey]

Thank you! Works fine; one question, tho: what is the "--" operator?
The formula doesn't work without them, but I can't find a ref. in

Take a look here:

http://www.mcgimpsey.com/excel/doubleneg.html