sumifs with month and year

Tony in Detroit

New Member
Joined
Jan 1, 2007
Messages
5
I'm trying to do a sumifs formula to sum a table of values, by month and year, based on a criteria. Normally I have no issue with sumif , but parsing out the month() and year() from the date is throwing me.

sumifs(income,status1," parse the year from date, parse the month from date)
 

Attachments

  • Capture.JPG
    Capture.JPG
    51.1 KB · Views: 32

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,022
Office Version
  1. 2016
Platform
  1. Windows
Hi Tony in Detroit,

I've added another row of data to force an actual sum for the SUMIFS. I'll let you add in the status check as you don't say what it should be checking.

Tony in Detroit.xlsx
ABCDEFGHI
1DateIncomeStatus120202021
210-Jun-20100openJanuary0300
310-Jul-20100openFebruary0700
410-Aug-20200closedMarch0200
510-Sep-20500closedApril00
601-Jan-21300closedMay00
701-Feb-21700openJune1990
801-Mar-21200closedJuly1000
901-Jun-2099closedAugust2000
10September5000
11October00
12November00
13December00
14Total9991200
Sheet1
Cell Formulas
RangeFormula
H2:I13H2=SUMIFS($B:$B,$A:$A,">="&DATE(H$1,MONTH($G2),1),$A:$A,"<"&EOMONTH(DATE(H$1,MONTH($G2),1),0)+1)
H14:I14H14=SUM(H2:H13)
G3:G13G3=EOMONTH(G2,0)+1
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,016
Office Version
  1. 365
Platform
  1. Windows
You can't parse the month and year from the dates in column A with sumifs or similar functions, you need to define a start and end date for the period and sum the values between those dates.

Assuming that your dates are in M/D/Y format, I think that this should work but I'm not able to test it with my regional settings.
Excel Formula:
=SUMIFS($B:$B,$C:$C,"Open",$A:$A,">="&DATEVALUE($G2&" 1 "&H$1),$A:$A,"<="&EOMONTH(DATEVALUE($G2&" 1 "&H$1),0))
Note that this will not work properly where the file is shared between users with different regional settings, e.g USA and UK.

edit:-

You will note that @Toadstool and I have used different methods of identifying the month. @Toadstool has assumed that your months are valid dates formatted as MMMM, while I have assumed that they are text names. Such details are not clear from screen captures, it is preferable that you use the XL2BB add in to post your examples as mini sheets for additional clarity (link in my signature at the foot of this post).
 
Last edited:

Tony in Detroit

New Member
Joined
Jan 1, 2007
Messages
5
Hi Tony in Detroit,

I've added another row of data to force an actual sum for the SUMIFS. I'll let you add in the status check as you don't say what it should be checking.

Tony in Detroit.xlsx
ABCDEFGHI
1DateIncomeStatus120202021
210-Jun-20100openJanuary0300
310-Jul-20100openFebruary0700
410-Aug-20200closedMarch0200
510-Sep-20500closedApril00
601-Jan-21300closedMay00
701-Feb-21700openJune1990
801-Mar-21200closedJuly1000
901-Jun-2099closedAugust2000
10September5000
11October00
12November00
13December00
14Total9991200
Sheet1
Cell Formulas
RangeFormula
H2:I13H2=SUMIFS($B:$B,$A:$A,">="&DATE(H$1,MONTH($G2),1),$A:$A,"<"&EOMONTH(DATE(H$1,MONTH($G2),1),0)+1)
H14:I14H14=SUM(H2:H13)
G3:G13G3=EOMONTH(G2,0)+1
Perfect. I was getting screwed up in both the syntax of the formula and formatting the formula was looking for.
 

Tony in Detroit

New Member
Joined
Jan 1, 2007
Messages
5

ADVERTISEMENT

You can't parse the month and year from the dates in column A with sumifs or similar functions, you need to define a start and end date for the period and sum the values between those dates.

Assuming that your dates are in M/D/Y format, I think that this should work but I'm not able to test it with my regional settings.
Excel Formula:
=SUMIFS($B:$B,$C:$C,"Open",$A:$A,">="&DATEVALUE($G2&" 1 "&H$1),$A:$A,"<="&EOMONTH(DATEVALUE($G2&" 1 "&H$1),0))
Note that this will not work properly where the file is shared between users with different regional settings, e.g USA and UK.

edit:-

You will note that @Toadstool and I have used different methods of identifying the month. @Toadstool has assumed that your months are valid dates formatted as MMMM, while I have assumed that they are text names. Such details are not clear from screen captures, it is preferable that you use the XL2BB add in to post your examples as mini sheets for additional clarity (link in my signature at the foot of this post).
This works also. I like having multiple solutions. At work I have to dumb down a lot of things I pull together for others, so they can figure out how to fix them, if they ever break.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,246
Office Version
  1. 365
Platform
  1. Windows
I like having multiple solutions.
I know that your question was specificall about SUMIFS, but if you have Excel 365** then another approach would be columns H:I below or columns L:M for all versions.

** I suggest that you 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’)

21 02 13.xlsm
ABCDEFGHIJKLM
1DateIncomeStatus12020202120202021
210-Jun-20100openJanuary00January00
310-Jul-20100openFebruary0700February0700
410-Aug-20200closedMarch00March00
510-Sep-20500closedApril00April00
601-Jan-21300closedMay00May00
701-Feb-21700openJune1000June1000
801-Mar-21200closedJuly1000July1000
901-Jun-2099closedAugust00August00
10September00September00
11October00October00
12November00November00
13December00December00
SUM
Cell Formulas
RangeFormula
H2:I13H2=SUM(FILTER($B$2:$B$9,(TEXT($A$2:$A$9,"mmmmyyyy")=$G2&H$1)*($C$2:$C$9="Open"),0))
L2:M13L2=SUMPRODUCT(--(TEXT($A$2:$A$9,"mmmmyyyy")=$K2&L$1),--($C$2:$C$9="Open"),$B$2:$B$9)
 

Tony in Detroit

New Member
Joined
Jan 1, 2007
Messages
5
I know that your question was specificall about SUMIFS, but if you have Excel 365** then another approach would be columns H:I below or columns L:M for all versions.

** I suggest that you 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’)

21 02 13.xlsm
ABCDEFGHIJKLM
1DateIncomeStatus12020202120202021
210-Jun-20100openJanuary00January00
310-Jul-20100openFebruary0700February0700
410-Aug-20200closedMarch00March00
510-Sep-20500closedApril00April00
601-Jan-21300closedMay00May00
701-Feb-21700openJune1000June1000
801-Mar-21200closedJuly1000July1000
901-Jun-2099closedAugust00August00
10September00September00
11October00October00
12November00November00
13December00December00
SUM
Cell Formulas
RangeFormula
H2:I13H2=SUM(FILTER($B$2:$B$9,(TEXT($A$2:$A$9,"mmmmyyyy")=$G2&H$1)*($C$2:$C$9="Open"),0))
L2:M13L2=SUMPRODUCT(--(TEXT($A$2:$A$9,"mmmmyyyy")=$K2&L$1),--($C$2:$C$9="Open"),$B$2:$B$9)
I have 1 version past 2003 at work, and 365 at home. It is a major pain having current software and learning new tricks, and not being able to use a lot of them, being in the dark ages, at work. :(
 

Forum statistics

Threads
1,144,698
Messages
5,725,822
Members
422,643
Latest member
elwayfan446

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