Small help with vlookup and sum

[Aniab]

This is what I'm trying to accomplish:
Lets say one sheet has this data:
Apple 10
Apple 10
Apple 12
Pear 23
I'd like to be able to have a function on sheet 2 that sums ALL the
numbers linked to the Apples:
Apples 32
Pears 23
etc.
I tried with VLookup, but it only returns a single value. Is there any
way to make it to return the whole sum?

Thanks.

 

[Don Guillett]

have a look in the help index for SUMIF

 

[via135]

hi!

try this one!

let's assume your data is in sheet1a1:b4!

enter
in sheet2 a1
=sumif(sheet1!a1:a4,"apple",sheet1!b1:b4)

in sheet2 a2
=sumif(sheet1!a1:a4,"pear",sheet1!b1:b4)


regds!

-via135

 

[Aniab]

Thank you!
That was easy

- Ania

 

[Kevin Vaughn]

Assuming you don't really need plurals on sheet 2 (apples and pears vs. apple
pear,) then try this formula:
=SUMIF(Sheet1!$A$1:$A$4,A1,Sheet1!$B$1:$B$4)
If you truly need to need them to be plural then try this array entered
formula (cntl-shift-enter rather than just enter):
=SUM(IF(Sheet1!$A$1:$A$4 & "s" = A3,Sheet1!$B$1:$B$4,0))

 

[Lila]

I just posted a question which is very similar to this and I'm wondering...
is there a way to generate the list of fruit on the second list without
typing it? Kind of like automatically applying a filter?

 

[Don Guillett]

glad to help