Help looking stuff up in columns

[Lisa]

I have a worksheet that I'd like it to be able to look at one column
for a specific thing, look in another column for a different specific
thing and then give me a sum of that pair in a seperate cell. I cannot
figure out what the formula is.

Apples 2001-1
Oranges 2001-2
Lemons 2001-3
Peaches 2001-4
Apples 2001-1
Apples 2001-2

There are 2 Apples with 2001-1.

Any suggestions would be greatly appreciated. Thanks.

 

[JulieD]

Hi Lisa

you can use the following formula
=SUMPRODUCT((A1:A6="Apples")*(B1:B6="2001-1"))
or
=SUMPRODUCT((A1:A6=D1)*(B1:B6=E1))
(where D1 contains Apples and E1 contains 2001-1)

Hope this helps
JulieD

 

[Frank Kabel]

Hi
try:
=SUMPRODUCT(--(A1:A100="Apples"),--(B1:B100="2001-1"))

 

[JulieD]

Hi Lisa

no, very unusual .. .the reason posting to multiple groups is discouraged is
that one person might have spent quite a bit of time working on an answer to
one of the posts only to discover that someone else has already answered the
post in a different group.

anyway, glad you've got a solution.

Cheers
JulieD

 

[Earl Kiosterud]

Lisa,

If you'll be wanting counts of each of all the combinations of fruit and
quarter, a pivot table will do that for you. Very worthwhile tool.