SUMIFs/IFs Beginner Assistance: Sum based on multiple values in IFS statement.

WingSystems

New Member
Joined
Aug 24, 2016
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Many blessings all -

Very beginner question.

I am attempting to do a SUMIFS statement with several parameters (screenshot below).

Goal is to complete a year to date calculation based on a value in Column C. For example, if Column C = January, sum January value...if Column C = February, sum January & February values.

Struggling and drawing a complete blank on this. Is IFS statement best used here? Used incorrectly?

=IF(A6<>"",SUMIFS(E:E,A:A,A6,D:D,D6,C:C,IFS(C6=January,"January", C6="February", January OR February, C6="March", January OR MARCH OR April….. )))

1588297676667.png
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
It would be good if the values in column C are actual first-of-the-month dates (e.g. 1/1/2020, 2/1/2020, etc; and maybe 8/1/2019, 9/1/2019, etc). You can use the Custom format "mmmm" without quotes to see only the month name.

Then your formula might be:

=IF(A6="", "", SUMIFS($E$5:$E$10000, $A$5:$A$10000, A6, $D$5:$D$10000, D6, $C$5:$C$10000, ">="&DATE(YEAR(TODAY()),1,1), $C$5:$C$10000, "<="&TODAY()))

Note that I changed whole-column references like A:A to ranges that should be large enough to include all future data. Change 10000 to something appropriate, if you wish.

In general, whole-column ranges can be risky. In some contexts, they cause Excel to look at 1M+ rows and/or create temp arrays of 1M+ data. Presumably you are not likely to have that much data.

SUMIFS might be smart enough to limit the number of rows. But it is best not to tempt fate and develop bad habits, IMHO.
 
Upvote 0
Thanks @joeu2004 . I should clarify year to date statement is not based on current date, rather just what month value (in general/text format) is in Column C. Unfortunately given scenario converting to actual date not ideal.

I know it may be gross, but would there be a method to do a SUMIF with the multiple text values mapped (i.e. January = January, February = January and February)?

I have read to use braces/curly brackets i.e. {January,February}
 
Upvote 0
@WingSystems
It would help if you could do two things:
  1. Update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
  2. Post your sample data with XL2BB so helpers can easily copy/paste it rather than having to manually type it all to test with.
 
Upvote 0
Thank you @Peter_SSs.

V1.2UseCostAnalysisYearOverYearReport.xlsm
ABCDEFGHIJKL
1DMA(All)
2Service Type(All)
3Month(All)
4
5Location NameLocationIdentiferMonthUOM Usage (2019) Usage (2020) Usage (% Variance)Cost (2019)Cost (2020)Cost (% Variance)Usage YTD (PreviousYear)
60000 - TestLocationMidwestJanuarykGal 12.0 11.2 -6.25119.74112.5-6.0512.0
70000 - TestLocationMidwestJanuarykWh 9,904.0 8,958.0 -9.55920.27838.18-8.929904.0
80000 - TestLocationMidwestFebruarykGal 15.0 14.2 -5148.69141.46-4.86
90000 - TestLocationMidwestFebruarykWh 9,839.0 9,320.0 -5.27940.69855.88-9.02
100000 - TestLocationMidwestMarchkGal 17.2 - -100170.410-100
110000 - TestLocationMidwestMarchkWh 9,072.0 0-100892.50-100
120000 - TestLocationMidwestAprilkGal 15.0 0-100148.690-100
130000 - TestLocationMidwestAprilkWh 11,116.0 0-1001046.440-100
140000 - TestLocationMidwestMaykGal 15.7 0-100155.930-100
150000 - TestLocationMidwestMaykWh 13,011.0 0-100694.10-100
160000 - TestLocationMidwestJunekGal 14.2 0-100141.460-100
170000 - TestLocationMidwestJunekWh 13,925.0 0-10013770-100
180000 - TestLocationMidwestJulykGal 15.0 0-100148.690-100
190000 - TestLocationMidwestJulykWh 14,293.0 0-1001381.020-100
200000 - TestLocationMidwestAugustkGal 14.2 0-100141.460-100
210000 - TestLocationMidwestAugustkWh 13,318.0 0-1001292.840-100
220000 - TestLocationMidwestSeptemberkGal 14.2 0-100141.460-100
230000 - TestLocationMidwestSeptemberkWh 14,469.0 0-1001300.110-100
240000 - TestLocationMidwestOctoberkGal 13.5 0-100134.220-100
250000 - TestLocationMidwestOctoberkWh 9,783.0 0-100870.850-100
260000 - TestLocationMidwestNovemberkGal 13.5 0-100134.220-100
270000 - TestLocationMidwestNovemberkWh 9,735.0 0-100886.180-100
280000 - TestLocationMidwestDecemberkGal 16.5 0-100163.170-100
290000 - TestLocationMidwestDecemberkWh 10,863.0 0-100946.030-100
30
Sheet2
Cell Formulas
RangeFormula
L6:L7L6=IF(A6<>"",SUMIFS(E:E,A:A,A6,D:D,D6,C:C,IFS(C6="January","January")))
 
Upvote 0
Thanks on both counts. :)

Now, does your version of 365 have the FILTER function?
(I'm with joeu2004 in strongly recommending that you do not use whole column references unless absolutely necessary or unless you are sure that it is not going to cause a performance issue with your worksheet/workbook. I have allowed 1000 rows in this example)

WingSystems 2020-05-01 1.xlsm
ABCDEKL
5Location NameLocationIdentiferMonthUOM Usage (2019) Usage YTD (PreviousYear)
60000 - TestLocationMidwestJanuarykGal11.96811.968
70000 - TestLocationMidwestJanuarykWh99049904
80000 - TestLocationMidwestFebruarykGal14.9626.928
90000 - TestLocationMidwestFebruarykWh983919743
100000 - TestLocationMidwestMarchkGal17.20444.132
110000 - TestLocationMidwestMarchkWh907228815
120000 - TestLocationMidwestAprilkGal14.9659.092
130000 - TestLocationMidwestAprilkWh1111639931
140000 - TestLocationMidwestMaykGal15.70874.8
150000 - TestLocationMidwestMaykWh1301152942
160000 - TestLocationMidwestJunekGal14.21289.012
170000 - TestLocationMidwestJunekWh1392566867
180000 - TestLocationMidwestJulykGal14.96103.972
190000 - TestLocationMidwestJulykWh1429381160
200000 - TestLocationMidwestAugustkGal14.212118.184
210000 - TestLocationMidwestAugustkWh1331894478
220000 - TestLocationMidwestSeptemberkGal14.212132.396
230000 - TestLocationMidwestSeptemberkWh14469108947
240000 - TestLocationMidwestOctoberkGal13.464145.86
250000 - TestLocationMidwestOctoberkWh9783118730
260000 - TestLocationMidwestNovemberkGal13.464159.324
270000 - TestLocationMidwestNovemberkWh9735128465
280000 - TestLocationMidwestDecemberkGal16.456175.78
290000 - TestLocationMidwestDecemberkWh10863139328
Sheet1
Cell Formulas
RangeFormula
L6:L29L6=SUM(FILTER(E$6:E$1000,(A$6:A$1000=A6)*(D$6:D$1000=D6)*(MONTH(1&C$6:C$1000)<=MONTH(1&C6)),0))
 
Last edited:
Upvote 0
You can also avoid any need to worry about row numbers at all if you turn the whole lot into a formal table (Insert Ribbon -> Table)

WingSystems 2020-05-01 1.xlsm
ABCDEL
5Location NameLocationIdentiferMonthUOM Usage (2019) Usage YTD (PreviousYear)
60000 - TestLocationMidwestJanuarykGal11.96811.968
70000 - TestLocationMidwestJanuarykWh99049904
80000 - TestLocationMidwestFebruarykGal14.9626.928
90000 - TestLocationMidwestFebruarykWh983919743
100000 - TestLocationMidwestMarchkGal17.20444.132
110000 - TestLocationMidwestMarchkWh907228815
120000 - TestLocationMidwestAprilkGal14.9659.092
130000 - TestLocationMidwestAprilkWh1111639931
140000 - TestLocationMidwestMaykGal15.70874.8
150000 - TestLocationMidwestMaykWh1301152942
160000 - TestLocationMidwestJunekGal14.21289.012
170000 - TestLocationMidwestJunekWh1392566867
180000 - TestLocationMidwestJulykGal14.96103.972
190000 - TestLocationMidwestJulykWh1429381160
200000 - TestLocationMidwestAugustkGal14.212118.184
210000 - TestLocationMidwestAugustkWh1331894478
220000 - TestLocationMidwestSeptemberkGal14.212132.396
230000 - TestLocationMidwestSeptemberkWh14469108947
240000 - TestLocationMidwestOctoberkGal13.464145.86
250000 - TestLocationMidwestOctoberkWh9783118730
260000 - TestLocationMidwestNovemberkGal13.464159.324
270000 - TestLocationMidwestNovemberkWh9735128465
280000 - TestLocationMidwestDecemberkGal16.456175.78
290000 - TestLocationMidwestDecemberkWh10863139328
Sheet2
Cell Formulas
RangeFormula
L6:L29L6=SUM(FILTER( [ Usage (2019) ] ,([Location Name]=[@[Location Name]])*([UOM]=[@UOM])*(MONTH(1&[Month])<=MONTH(1&[@Month])),0))
 
Upvote 0
Now, does your version of 365 have the FILTER function?
If not, convert either of the above to SUMPRODUCT (definitely do not use whole column references with this function!)

WingSystems 2020-05-01 1.xlsm
ABCDEKL
5Location NameLocationIdentiferMonthUOM Usage (2019) Usage YTD (PreviousYear)
60000 - TestLocationMidwestJanuarykGal11.96811.968
70000 - TestLocationMidwestJanuarykWh99049904
80000 - TestLocationMidwestFebruarykGal14.9626.928
90000 - TestLocationMidwestFebruarykWh983919743
100000 - TestLocationMidwestMarchkGal17.20444.132
110000 - TestLocationMidwestMarchkWh907228815
120000 - TestLocationMidwestAprilkGal14.9659.092
130000 - TestLocationMidwestAprilkWh1111639931
140000 - TestLocationMidwestMaykGal15.70874.8
150000 - TestLocationMidwestMaykWh1301152942
160000 - TestLocationMidwestJunekGal14.21289.012
170000 - TestLocationMidwestJunekWh1392566867
180000 - TestLocationMidwestJulykGal14.96103.972
190000 - TestLocationMidwestJulykWh1429381160
200000 - TestLocationMidwestAugustkGal14.212118.184
210000 - TestLocationMidwestAugustkWh1331894478
220000 - TestLocationMidwestSeptemberkGal14.212132.396
230000 - TestLocationMidwestSeptemberkWh14469108947
240000 - TestLocationMidwestOctoberkGal13.464145.86
250000 - TestLocationMidwestOctoberkWh9783118730
260000 - TestLocationMidwestNovemberkGal13.464159.324
270000 - TestLocationMidwestNovemberkWh9735128465
280000 - TestLocationMidwestDecemberkGal16.456175.78
290000 - TestLocationMidwestDecemberkWh10863139328
Sheet3
Cell Formulas
RangeFormula
L6:L29L6=SUMPRODUCT(E$6:E$1000,--(A$6:A$1000=A6),--(D$6:D$1000=D6),--(MONTH(1&C$6:C$1000)<=MONTH(1&C6)))



WingSystems 2020-05-01 1.xlsm
ABCDEL
5Location NameLocationIdentiferMonthUOM Usage (2019) Usage YTD (PreviousYear)
60000 - TestLocationMidwestJanuarykGal11.96811.968
70000 - TestLocationMidwestJanuarykWh99049904
80000 - TestLocationMidwestFebruarykGal14.9626.928
90000 - TestLocationMidwestFebruarykWh983919743
100000 - TestLocationMidwestMarchkGal17.20444.132
110000 - TestLocationMidwestMarchkWh907228815
120000 - TestLocationMidwestAprilkGal14.9659.092
130000 - TestLocationMidwestAprilkWh1111639931
140000 - TestLocationMidwestMaykGal15.70874.8
150000 - TestLocationMidwestMaykWh1301152942
160000 - TestLocationMidwestJunekGal14.21289.012
170000 - TestLocationMidwestJunekWh1392566867
180000 - TestLocationMidwestJulykGal14.96103.972
190000 - TestLocationMidwestJulykWh1429381160
200000 - TestLocationMidwestAugustkGal14.212118.184
210000 - TestLocationMidwestAugustkWh1331894478
220000 - TestLocationMidwestSeptemberkGal14.212132.396
230000 - TestLocationMidwestSeptemberkWh14469108947
240000 - TestLocationMidwestOctoberkGal13.464145.86
250000 - TestLocationMidwestOctoberkWh9783118730
260000 - TestLocationMidwestNovemberkGal13.464159.324
270000 - TestLocationMidwestNovemberkWh9735128465
280000 - TestLocationMidwestDecemberkGal16.456175.78
290000 - TestLocationMidwestDecemberkWh10863139328
Sheet4
Cell Formulas
RangeFormula
L6:L29L6=SUMPRODUCT( [ Usage (2019) ] ,--([Location Name]=[@[Location Name]]),--([UOM]=[@UOM]),--(MONTH(1&[Month])<=MONTH(1&[@Month])))
 
Upvote 0
Thank you! @Peter_SSs

Unfortunately I do not have the filter function yet on my 365. Mmy O365 updated today and was hoping that update would come through to no avail :(.
 
Upvote 0
Unfortunately I do not have the filter function yet on my 365. Mmy O365 updated today and was hoping that update would come through to no avail :(.
You are probably on the agenda to get the FILTER function in (or soon after) July. However, I have given you some SUMPRODUCT alternatives that should work now.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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
Back
Top