Hi and function help please

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

idaho@idaho.net - 17 Aug 2006 05:09 GMT

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!

Reply to this Message

Biff - 17 Aug 2006 05:33 GMT

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

> My worksheet looks likes this:
>
[quoted text clipped - 12 lines]
>
> thank you!

Reply to this Message

idaho@idaho.net - 17 Aug 2006 05:45 GMT

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

> Hi!
>
[quoted text clipped - 27 lines]
>>
>> thank you!

Reply to this Message

idaho@idaho.net - 17 Aug 2006 05:48 GMT

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..
[quoted text clipped - 36 lines]
>>>
>>> thank you!

Reply to this Message

Biff - 17 Aug 2006 05:57 GMT

> 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

> Thanks Biff!
> Doesnt work pasting it in but I am sure it is just that I am a novice..
[quoted text clipped - 36 lines]
>>>
>>> thank you!

Reply to this Message