SUMIF problem

[dawsona]

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.)

 

[DominicB]

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

 

[dawsona]

Thanks Dominic.

 

[PaddyD]

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
	A	B	C	D	E	F	G	H
1	Item	Jan	Feb	Mar	Apr	May	June
2	a	1	2	3	4	5	6
3	b	7	8	9	10	11	12
4	a	13	14	15	16	17	18
5	c	19	20	21	22	23	24
6
7
8	For	a
9	From	Jan
10	To	May
11
12	Total	90
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()