Count with two criteria in a second worksheet
 

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

Count with two criteria in a second worksheet
 

Try this:

A1 = apple
A2 = pear

Entered in B1 and copied down:

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

--
Biff
Microsoft Excel MVP


"bec" wrote in message
...
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