SUMIF with more than 1 sum range

[PATSYS]

I am working with data that has monthly figures (Jan-Dec) but no column for total year. Adding a new column is not an option because it will be overwritten when new data somes in.

Is there a way that I can do a sumif with several sumranges? The ranges are exactly the same sizes.

Thanks

 

[Peter_SSs]

PATSYS

Any chance of some sample data and expected results?

 

[Aladin Akyurek]

Your spec is too general. If you have one condition range and several ranges to sum, you have to switch to a formula that can operates on arrays... Example:

=SUM(IF($A$2:$A$40=X2,$E$2:$H$40))

confirmed with control+shift+enter, would total every numeric record in E2:H40 whenever A2:A40 is equal to X2, the condition/criterion.

 

[PATSYS]

Hi guys,

Sorry for not being very detailed, not sure if Aladin's suggestion above will work. Let me explain.

Here is a simplified data for illustration:

Book2
	A	B	C	D	E
1	Region	Model	Jan	Feb	Mar
2	North	A	1	6	7
3	South	B	2		7
4	East	C	3	5
5	West	D		4	8
6	North	A	1
Sheet1

I another sheet, I am asked to report the average figures for Q1 for each region+model. So for instance, for Region NORTH & Model A, I should get a figure of 5.

If I use the formula:

=SUM(IF($A$2:$A$6=A13,$B$2:$B$6=B13,$C$2:$E$6))/3

I get 9.6667 which is incorrect.

Thanks

 

[PATSYS]

Hi Domenic,

The average should be 4.33333 (my prev figure of 5 was a mistake).

This is so because the total in Jan, Feb and Mar for region North+Model A are 2, 4, 7. Total 13, divived by 3 months. This will be the same is I computed each row divided by 3 and do a sumif of the result.

Thanks

 

[Aladin Akyurek]

If that's correct simplification then:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Region	Model	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep
2	North	A	1	6	7	10	5
3	South	B	2		7	2	9
4	East	C	3	5		10	6
5	West	D		4	8	6	11
6	North	A	1			3	6
7
8
9
10
11	Region	Model	Quarter	Avg
12	North	A	Q2	8
Sheet1

D12:

=SUM(IF($A$2:$A$6=A12,IF($B$2:$B$6=B12,OFFSET($C$2:$K$6,0,LOOKUP(C12,{"Q1",3;"Q2",6;"Q3",9;"Q4",12})-3,,3))))/3

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