Sumif using match when target is not on same row...

**shhhhh22** · 12-29-2011, 01:22 PM

Hello all,

On sheet2 i have a list of six values:

cat
dog
mouse
tiger
giraffe
fox

On sheet1 I have over 10,000 records that will contain 1 of the above values.

In words here is what I want to do...

in the column next to cat. find every value on sheet1 column A that contains cat then return the sum from column H of those values only. The problem is i need excel to look at column H offset by one row.
So if on sheet1 "Cat" is found in cell A14 i need it to sum the value in H15

I hope what I'm asking makes sense.

I thank you all for your help in this great forum.
I look forward to your replies!

**Andrew-R** · 12-29-2011, 01:28 PM

Assuming your sheet 1 is called Sheet1 and that the data on sheet 2 begins in cell A1 then on sheet 2 try:

=SUMPRODUCT(--('Sheet1'!$A$1:$A$11000=A1),'Sheet1'!$H$2:$H$11001)

Might not be the quickest formula in the world.