# SUMIF problem

#### dawsona

##### Board Regular
I have a spreadsheet with a list of fruit in column A and the months Jan - Dec in the next twelve columns.

If I use SUMIF(\$A\$2:\$A\$10,"Apples",\$B\$2:\$B\$10) it gives me the correct total for Apples for January. If I change this to:

SUMIF(\$A\$2:\$A\$10,"Apples",\$B\$2:\$C\$10) to get the total for Jan & Feb it still returns the Jan total only.

Is there a way to do this? (I don't want to use xla add-ins.)

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Good evening dawsona

There are a couple of ways of doing this, but I like array formulae so that's going to be the way that I would go.

=SUM(IF(A2:A10="Apples",B3:C10,0))

But as it's an array formula don't use ENTER to commit, but SHIFT + CTRL + ENTER and it should work perfectly.

BTW, if the add-in you're refering to is the Conditional Sum add-in, then try using it - it actually builds the formula for you and isn't dependant on the add-in, so if someone without the add-in opens your spreadsheet it'll still work.

HTH

DominicB

Thanks Dominic.

Although you've got an answer, you might be interested in a more general approach - you don't want to be going into the formula every time you want to change the range to sum:
Book2
ABCDEFGH
1ItemJanFebMarAprMayJune
2a123456
3b789101112
4a131415161718
5c192021222324
6
7
8Fora
9FromJan
10ToMay
11
12Total90
Sheet1

formula is:

=SUM(IF(A1:A5=B8,INDEX(B1:G5,,MATCH(B9,B1:G1,0)):INDEX(B1:G5,,MATCH(B10,B1:G1,0))))

...which needs to be array entered with control + shift + enter, not just enter.

by way of brief explanation, the index():index() bit identifies which columns we're interested in, using match() formulas to get the start & end column positions. the result is the range we need, which is fed into the arrayed sum()

Replies
3
Views
122
Replies
3
Views
190
Replies
4
Views
212
Replies
20
Views
931
Replies
5
Views
129

1,207,288
Messages
6,077,546
Members
446,287
Latest member
lihong3210

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