Vlookup, HLOOKUP, To find Multiple items and then sum all in colum

B

Bobberjoe

I am working in sheet 3 and want to look up all items that match column b on
sheet 1 and then sum up column c on sheet a and return that value in sheet 3
Here is a sample
Sheet 3

A B C
400 (look up all 400s(B) on sheet 1 and return the sum of all the
values in col C)
1000
1200
 
J

JBeaucaire

This appears to be a SUMIF:

=SUMIF(Sheet1!$A$1:$A$1000,Sheet3!A2,Sheet1!$C$1:$C$1000)

Something like that. That assumes the "400" you wanted was in cell A2.
 
B

Bobberjoe

It worked, great. THANKS!
I also got the same result using,
'=SUMPRODUCT(('Sheet1!$E$3:$E$243=400)*('SHEET1'!F6:F246='SHEET1'!F6:F246)*('SHEET1'!F6:F246))

But your formula I understand how it worked. Thanks, again.
 
P

Pete_UK

Not sure why you need this term in the middle:

('SHEET1'!F6:F246='SHEET1'!F6:F246)

You could try it this way:

=SUMPRODUCT(--('Sheet1!$E$3:$E$243=400),­('SHEET1'!F6:F246))

but generally SUMIF will be faster than SUMPRODUCT if you only have
one criteria.

Your second array is 3 cells down from your first array.

Hope this helps.

Pete
 
J

JBeaucaire

Anytime you can use SUMIF instead of SUMPRODUCT, do it, much less strain on
the sheet performance. Good luck.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top