Hi and function help please

[Guest]

My worksheet looks likes this:

A B
1 Name Fruit
2 Sam Apple
3 Sam Apple
4 Sam Apple
5 Sam Orange
6 Jane Orange
7 Jane Apple
8 Jim Orange
9 Jim Orange

I would like a function/formula that tells me how many Apples Sam has.

thank you!

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(A2:A9="Sam"),--(B2:B9="Apple"))

Better to use cells to hold the criteria:

D1 = Sam
E1 = Apple

=SUMPRODUCT(--(A2:A9=D1),--(B2:B9=E1))

Biff

 

[Guest]

Thanks Biff!
Doesnt work pasting it in but I am sure it is just that I am a novice..
In your formula, =SUMPRODUCT(--(A2:A9=D1),--(B2:B9=E1))

what does the "--" between SUMPRODUCT( and (A2:... represent?

Thanks!
Andy

 

[Guest]

Actually, IT DOES work ! THANK YOU !!!!!

Thanks Biff!
Doesnt work pasting it in but I am sure it is just that I am a novice..
In your formula, =SUMPRODUCT(--(A2:A9=D1),--(B2:B9=E1))

what does the "--" between SUMPRODUCT( and (A2:... represent?

Thanks!
Andy

 

[Biff]

what does the "--" between SUMPRODUCT( and (A2:... represent?

Each of these expressions will return an array of TRUE's or FALSE's:

(A2:A9=D1)
(B2:B9=E1)

The "--" converts those to 1's and 0's. --TRUE = 1, --FALSE = 0

Sumproduct then sums those numbers for the result.

See this for a detailed explanation:

http://xldynamic.com/source/xld.SUMPRODUCT.html

Biff