sumifs with month and year

[Tony in Detroit]

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)

 

[Toadstool]

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
	A	B	C	D	E	F	G	H	I
1	Date	Income	Status1					2020	2021
2	10-Jun-20	100	open				January	0	300
3	10-Jul-20	100	open				February	0	700
4	10-Aug-20	200	closed				March	0	200
5	10-Sep-20	500	closed				April	0	0
6	01-Jan-21	300	closed				May	0	0
7	01-Feb-21	700	open				June	199	0
8	01-Mar-21	200	closed				July	100	0
9	01-Jun-20	99	closed				August	200	0
10							September	500	0
11							October	0	0
12							November	0	0
13							December	0	0
14							Total	999	1200
Sheet1

Cell Formulas
Range	Formula
H2:I13	H2	=SUMIFS($B:$B,$A:$A,">="&DATE(H$1,MONTH($G2),1),$A:$A,"<"&EOMONTH(DATE(H$1,MONTH($G2),1),0)+1)
H14:I14	H14	=SUM(H2:H13)
G3:G13	G3	=EOMONTH(G2,0)+1

 

[jasonb75]

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.

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

 

[Tony in Detroit]

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
	A	B	C	D	E	F	G	H	I
1	Date	Income	Status1					2020	2021
2	10-Jun-20	100	open				January	0	300
3	10-Jul-20	100	open				February	0	700
4	10-Aug-20	200	closed				March	0	200
5	10-Sep-20	500	closed				April	0	0
6	01-Jan-21	300	closed				May	0	0
7	01-Feb-21	700	open				June	199	0
8	01-Mar-21	200	closed				July	100	0
9	01-Jun-20	99	closed				August	200	0
10							September	500	0
11							October	0	0
12							November	0	0
13							December	0	0
14							Total	999	1200
Sheet1

Cell Formulas
Range	Formula
H2:I13	H2	=SUMIFS($B:$B,$A:$A,">="&DATE(H$1,MONTH($G2),1),$A:$A,"<"&EOMONTH(DATE(H$1,MONTH($G2),1),0)+1)
H14:I14	H14	=SUM(H2:H13)
G3:G13	G3	=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]

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.

=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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Income	Status1					2020	2021			2020	2021
2	10-Jun-20	100	open				January	0	0		January	0	0
3	10-Jul-20	100	open				February	0	700		February	0	700
4	10-Aug-20	200	closed				March	0	0		March	0	0
5	10-Sep-20	500	closed				April	0	0		April	0	0
6	01-Jan-21	300	closed				May	0	0		May	0	0
7	01-Feb-21	700	open				June	100	0		June	100	0
8	01-Mar-21	200	closed				July	100	0		July	100	0
9	01-Jun-20	99	closed				August	0	0		August	0	0
10							September	0	0		September	0	0
11							October	0	0		October	0	0
12							November	0	0		November	0	0
13							December	0	0		December	0	0
SUM

Cell Formulas
Range	Formula
H2:I13	H2	=SUM(FILTER($B$2:$B$9,(TEXT($A$2:$A$9,"mmmmyyyy")=$G2&H$1)*($C$2:$C$9="Open"),0))
L2:M13	L2	=SUMPRODUCT(--(TEXT($A$2:$A$9,"mmmmyyyy")=$K2&L$1),--($C$2:$C$9="Open"),$B$2:$B$9)

 

[Tony in Detroit]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Income	Status1					2020	2021			2020	2021
2	10-Jun-20	100	open				January	0	0		January	0	0
3	10-Jul-20	100	open				February	0	700		February	0	700
4	10-Aug-20	200	closed				March	0	0		March	0	0
5	10-Sep-20	500	closed				April	0	0		April	0	0
6	01-Jan-21	300	closed				May	0	0		May	0	0
7	01-Feb-21	700	open				June	100	0		June	100	0
8	01-Mar-21	200	closed				July	100	0		July	100	0
9	01-Jun-20	99	closed				August	0	0		August	0	0
10							September	0	0		September	0	0
11							October	0	0		October	0	0
12							November	0	0		November	0	0
13							December	0	0		December	0	0
SUM

Cell Formulas
Range	Formula
H2:I13	H2	=SUM(FILTER($B$2:$B$9,(TEXT($A$2:$A$9,"mmmmyyyy")=$G2&H$1)*($C$2:$C$9="Open"),0))
L2:M13	L2	=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.

 

[Peter_SSs]

I have 1 version past 2003 at work, and 365 at home.

You can enter multiple versions in your Account Details.