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

  • Thread starter Thread starter Bobberjoe
  • Start date Start date
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
 
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.
 
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.
 
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
 
Anytime you can use SUMIF instead of SUMPRODUCT, do it, much less strain on
the sheet performance. Good luck.
 
Back
Top