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

[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

 

[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.

 

[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.

 

[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

 

[JBeaucaire]

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