# sumif with range over multiple row and column

#### Krevis

##### New Member
 A B C D E F 1 2013 2014 2015 2016 2 James Peter 100 formula in question 3 Peter James 108 formula in question 4 Mary Peter James 100 formula in question

<tbody>
</tbody>

I can't even think of proper question title. Hope i got it right.

How do I have something like sumif(\$B\$2:\$E\$4,A2,F:F) where E4 is based on number of year starting from 2013?

So... for criteria of 4 years
James would have 108+100
Peter would have 100+100
but Mary would have 0.

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Chrisdontm

##### Well-known Member
One rule with SUMIF is that the sum range has to be the same size as the criteria range.
So you can't have it look up the whole column when you are just telling it to consider a small range.

So try this in cell: G2 and then copy it on down.....;

=SUMIF(\$B\$2:\$B\$4,\$A2,\$F1:\$F3)+SUMIF(\$C\$2:\$C\$4,\$A2,\$F1:\$F3)+SUMIF(\$D\$2:\$D\$4,\$A2,\$F1:\$F3)+SUMIF(\$E\$2:\$E\$4,\$A2,\$F1:\$F3)

There may be a shorter way to make this work, but at least this will work...

##### MrExcel MVP
In F2 control+shift+enter and copy down:

=SUM(IF(MMULT((\$B\$2:\$E\$4=\$A2)+0,TRANSPOSE(COLUMN(\$B\$2:\$E\$4)^0)),\$F\$2:\$F\$4))

This approach becomes interesting in case you have more year columns to consider.

Replies
3
Views
605
Replies
3
Views
592
Replies
6
Views
482
Replies
1
Views
161
Replies
11
Views
287

1,190,775
Messages
5,982,846
Members
439,799
Latest member
matts12

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