counting cells per content

[gmkfusion]

sample worksheet

........A.............B
1.....yes
2......no
3......no
4.....................text
5
6.....................text
7......no

from the above, I want to get a count of cells that return a "no" fro
field A, plus a count of cells that are empty in field A but hav
content in the next cell to right, field B. So, for the above I shoul
get a total of 5. I did it with VBA, but I would like to see how to d
it using Excel functions.

Any help, thanks,
gmkfusio

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((A1:A100="no")+((A1:A100="")*(B1:B100<>"")))

 

[Bob Phillips]

=SUMPRODUCT((A1:A100="no")+((A1:A100="")*(B1:B100<>"")))

But be aware this will count if A is no and B not empty, may or may not be
what you want.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[gmkfusion]

Thank you very much Frank, this is exactly what I wanted.
Great reply time, too!

gmkfusio

 

[gmkfusion]

Thanks, Bob

but if A is "no", I do want an increment, regardless if B has conten
or not. total is based A, then B.

Thank You for your insight.
gmkfusio