SUMIF with more than 1 sum range

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,749
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,531
Office Version
  1. 365
Platform
  1. Windows
PATSYS

Any chance of some sample data and expected results?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Mar 12, 2006
Messages
1,749
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
ABCDE
1RegionModelJanFebMar
2NorthA167
3SouthB27
4EastC35
5WestD48
6NorthA1
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:

Code:
=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

Well-known Member
Joined
Mar 12, 2006
Messages
1,749
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
If that's correct simplification then:
Book1
ABCDEFGHIJK
1RegionModelJanFebMarAprMayJunJulAugSep
2NorthA167105
3SouthB2729
4EastC35106
5WestD48611
6NorthA136
7
8
9
10
11RegionModelQuarterAvg
12NorthAQ28
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.
 

Forum statistics

Threads
1,141,095
Messages
5,704,311
Members
421,338
Latest member
Pepess

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top