Specify multiple rows for SUMIF range?

**necht_angel** · 11-16-2011, 12:13 PM

Hi there, I have been trying to get excel to lookup two rows in the formula range, but when I do this the formula stops working altogether. Please see attached example of what I mean.

Be grateful for any help on this one as it's got me scratching my head.

Thanks very much.

Kate :-)

**NBVC** · 11-16-2011, 12:20 PM

Your first range was different in that I14 cell.

Try in G8:

=SUMIF($A$2:$F$2,G$2,$A8:$F9)

then just copy down and across the matrix

i don't understand the data validation question in your worksheet comment.

**necht_angel** · 11-16-2011, 12:34 PM

Hi, thanks. What I want is the formula in cell I14 to work, and to apply it to all the other cells. I want to incorporate both rows in the lookup range, but that doesn't seem to work, and I can't figure out why the formula in cell I14 won't work. Can I not lookup more than one row?

Thanks NBVC

Kate

**daddylonglegs** · 11-16-2011, 12:36 PM

SUMIF makes the sum range the same size as the criteria range (implicitly - even if you don't specify it that way explicitly).

I think that you may want to specify 1st in G2 and still pick up the value from A8? Try SUMPRODUCT, i.e. this formula in G8

=SUMPRODUCT(($A$1:$F$2=G$2)*$A8:$F8)

copied across and down

**NBVC** · 11-16-2011, 01:05 PM

The SUMIF formula provides the same results....

Enter either formula in G8, then copy down and across... I14 will then have a value....

**daddylonglegs** · 11-16-2011, 01:26 PM

Originally Posted by NBVC

The SUMIF formula provides the same

.....not if you select "1st" from the dropdown in G2.....

**NBVC** · 11-16-2011, 01:33 PM

Ah yes, thanks... I guess I misinterpreted...

**necht_angel** · 11-17-2011, 04:15 AM

Originally Posted by daddylonglegs

SUMIF makes the sum range the same size as the criteria range (implicitly - even if you don't specify it that way explicitly).

I think that you may want to specify 1st in G2 and still pick up the value from A8? Try SUMPRODUCT, i.e. this formula in G8

=SUMPRODUCT(($A$1:$F$2=G$2)*$A8:$F8)

copied across and down

That works brilliantly, thanks so much, perfect.

Kate :-)