Count with two criteria in a second worksheet

B

bec

I'm trying to count columns in a second worksheet. 1) If column A is = apple,
then I want to count the entries in column B which are greater than "0"
sheet 2
apple 2
apple 10
apple 0
apple 3
pear 2
pear 0
pear 5
overall the count for apple would be 3. If I do the same thing for pear it
would be 2.
Thanks for your help
 
T

T. Valko

Try this:

A1 = apple
A2 = pear

Entered in B1 and copied down:

=SUMPRODUCT(--(Sheet2!A$1:A$7=A1),--(Sheet2!B$1:B$7>0))
 

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

Create a text based count with criteria from two differnt workbook 4
count if using columns not range of cells 7
vlookup 3 sheets 1
COUNTIF Multiple Values 3
counting items within a date range 1
sumif 1
translating countifs function to Excel 2003 4
V-lookup and Countif 2

Top