Sumif multiple rows, single criteria

[iambasil]

Maybe this:

=SUMPRODUCT(($D$14:$BS$14=D$368)*($D184:$BS185))

Split from: https://www.mrexcel.com/forum/excel-questions/388098-sumif-multiple-rows-single-criteria.html

I need to do the same as this user, but with the difference being that the rows aren't in a single range.

So I'm basically looking at doing something like:
=SUMPRODUCT(($D$14:$BS$14=D$368)*($D184:$BS185,$D200:$BS200,$D203:$BS203))

So in the example, based on the criteria matching in row 14, I'd want to add up the cells of rows 184,185,200, and 203.

The example formula I typed won't work - anyone know how I should do it?

Cheers,

B

 

[Eric W]

Welcome to the forum.

Sometimes the obvious way is best, even if the formula is longer than it could be:

=SUMIF($D$14:$BS$14,D$368,$D$184:$BS$184)+SUMIF($D$14:$BS$14,D$368,$D$185:$BS$185)+SUMIF($D$14:$BS$14,D$368,$D$200:$BS$200)+SUMIF($D$14:$BS$14,D$368,$D$203:$BS$203)

Four separate SUMIFs, but very clear what's going on. If you want a shorter formula, you could use this array formula:

=SUM(IF($D$14:$BS$14=D$368,$D$184:$BS$184+$D$185:$BS$185+$D$200:$BS$200+$D$203:$BS$203))

and press Control+Shift+Enter when you enter the formula in the formula bar.

Or even:

=SUM(SUMIF(D14:BS14,D368,OFFSET(D184:BS203,{0,1,16,19},0)))

also with CSE.

Or

=SUMPRODUCT(SUMIF(D14:BS14,D368,OFFSET(D184:BS203,{0,1,16,19},0)))

without CSE.

 

[Eric W]

It doesn't seem to hurt anything, but that last formula (and the one above it) really should be:

=SUMPRODUCT(SUMIF(D14:BS14,D368,OFFSET(D184:BS[COLOR=#ff0000]184[/COLOR],{0,1,16,19},0)))