# Sumif multiple rows, single criteria

#### iambasil

##### New Member
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

Last edited by a moderator:

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Welcome to the forum.

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

Code:
``=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:

Code:
``=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:

Code:
``=SUM(SUMIF(D14:BS14,D368,OFFSET(D184:BS203,{0,1,16,19},0)))``
also with CSE.

Or

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

without CSE.

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

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

Replies
3
Views
194
Replies
0
Views
54
Replies
1
Views
371
Replies
3
Views
2K
Replies
3
Views
88

1,203,111
Messages
6,053,571
Members
444,673
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back