This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi,
I am trying use the sumif function on a rectangular range as opposed to a range of single column. Formulae seems to be working only for one column.
In the below example, I need to get the total for Jan. I used the formulae =SUMIF(B11:D15,B11,C11:D15) and the answer is 225 and not 675.
Appreciate any help - thanks.
SS
| s1 | s2 | |
|---|---|---|
| JAN | 100 | 200 |
| JAN | 125 | 250 |
| FEB | 1100 | 2200 |
| FEB | 368 | 736 |
| FEB | 25 | 50 |
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
SUMIF() and SUMIFS() were not intended to sum multiple columns. Try this SUMPRODUCT() formula instead,
=SUMPRODUCT((B11:B15=B11)*C11:D15)
| SUMIF function |
|---|
| SUMIFS function |
| SUMPRODUCT function |
Answer accepted by question author
Use sumproduct formula in case you need to get sum of more than 1 column
=SUMPRODUCT((B11:B15=B11)*C11:D15)
Many thanks to all !
You could use =SUMIF(B11:B15,B11,C11:C15)+SUMIF(B11:B15,B11,D11:D15), alternatively use the array formula:
=SUM(IF(B11:B15=B11,C11:D15))
Press Ctrl+Shift+Enter to enter the formula as an array formula.