countif for empty values

D

dolphinv4

Hi,

I have a set of data:

A B
apple 3
orange 5
pear 7
apple
apple 2

I want to count the number of times Apple appears AND
column B is not empty. I tried to use

=countif(A:A,and("apple",NOT(B:B="")))

but it doesn't work. How do I get around this problem?

Thanks.
Val
 
J

JE McGimpsey

One way:

=SUMPRODUCT(--(A1:A1000="Apple"),--(B1:B1000<>""))

The -- convert the boolean (TRUE/FALSE) values to 1/0, respectively,
since SUMPRODUCT requires numeric arguments.
 
S

Soo Cheon Jheong

Hi,

=SUMPRODUCT((A1:A1000="Apple")*N(B1:B1000<>""))

--
Regards,
Soo Cheon Jheong
_ _
^ ^
~
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting a mixed text/number column based on text in another colum 3
Lookup 5
SEARCH but for cell contents rather than text 2
series of sums in a long spreadsheet 1
Matching column data 3
How to choose maximum value 7
How do I lookup only Apple and return value on Price Column and su 2
Using COUNTIF with Two or more Columns as Criteria 12

Top