SumIFS Question for Dates

[Guzzlr]

Hello
Using SumIFS, one of my criteria_range is in the foramt of Date mm/dd/yyyy. However, the Criteria is just the month
For example:
In column F:F I have all dates of 2023 and 2024 in mm/dd/yyyy.
In column A:A I have the months abriviated: Oct, Nov, Dec, Jan...so on
I wish to be able to use Nov, as my criteria for the criteria_range of F:F with the format of mm/dddd/2023. so all numbers in my Sum_Range would be found and added for Nov of 2023.

 

[Joe4]

Please post a sample of your data and expected results for us to see.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Guzzlr]

1st Quarter 2024_CM-PM_Active vs Complete Worksheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2									3A - High Sustain	3B - Medium Sustain	3C - Low Sustain	3A - High Sustain	3B - Medium Sustain	3C - Low Sustain
3
4
5	Year	Month	Active	Completed	Month	Date	Active	Completed	Active	Active	Active	Completed	Completed	Completed
6	2023	Oct	29	286	Oct	10/1/2023	0	1	0	0	0	0	1	0
7	2023	Nov	45	310	Oct	10/2/2023	2	11	0	1	1	3	5	3
8	2023	Dec	65	324	Oct	10/3/2023	1	17	0	1	0	2	11	4
9	2024	Jan	126	524	Oct	10/4/2023	0	8	0	0	0	0	4	4
10	2024	Feb	163	325	Oct	10/5/2023	0	10	0	0	0	0	7	3
11	2024	Mar	10	0	Oct	10/6/2023	2	12	0	1	1	2	5	5
12	2024	Apr	0	0	Oct	10/7/2023	0	1	0	0	0	1	0	0
13					Oct	10/8/2023	0	1	0	0	0	1	0	0
14					Oct	10/9/2023	0	4	0	0	0	2	1	1
15					Oct	10/10/2023	0	7	0	0	0	0	5	2
16					Oct	10/11/2023	1	20	0	1	0	0	14	6
17					Oct	10/12/2023	0	21	0	0	0	5	11	5
18					Oct	10/13/2023	0	7	0	0	0	3	2	2
Data-CM

Cell Formulas
Range	Formula
C6:C12	C6	=SUMIF($E$6:$E$500,B6,$G$6:$G$5000)
D6:D12	D6	=SUMIF($E$6:$E$500,B6,$H$6:$H$5000)
G6:G18	G6	=SUM(I6:K6)
H6:H18	H6	=SUM(L6:N6)
I6:I18	I6	=COUNTIFS(NexGen!$M:$M,'Data-CM'!F6,NexGen!$E:$E,'Data-CM'!$I$5,NexGen!$D:$D,'Data-CM'!$I$2)
J6:J18	J6	=COUNTIFS(NexGen!$M:$M,'Data-CM'!F6,NexGen!$E:$E,'Data-CM'!$J$5,NexGen!$D:$D,'Data-CM'!$J$2)
K6:K18	K6	=COUNTIFS(NexGen!$M:$M,'Data-CM'!F6,NexGen!$E:$E,'Data-CM'!$K$5,NexGen!$D:$D,'Data-CM'!$K$2)
L6:L18	L6	=COUNTIFS(NexGen!$M:$M,'Data-CM'!F6,NexGen!$E:$E,'Data-CM'!$L$5,NexGen!$D:$D,'Data-CM'!$L$2)
M6:M18	M6	=COUNTIFS(NexGen!$M:$M,'Data-CM'!F6,NexGen!$E:$E,'Data-CM'!$M$5,NexGen!$D:$D,'Data-CM'!$M$2)
N6:N18	N6	=COUNTIFS(NexGen!$M:$M,'Data-CM'!F6,NexGen!$E:$E,'Data-CM'!$N$5,NexGen!$D:$D,'Data-CM'!$N$2)
A6:A8	A6	=TEXT(F6,"yyyy")
E6:E18	E6	=TEXT(F6,"mmm")

 

[Guzzlr]

So you can see where I wish to have my SumIf be able to include the year in column F, instead of having to individually write in the Year and month in column A&B.
Thank you

 

[Alex Blakenburg]

Will this work for you ?

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2									3A - High Sustain	3B - Medium Sustain	3C - Low Sustain	3A - High Sustain	3B - Medium Sustain	3C - Low Sustain
3
4
5	Year	Month	Active	Completed	Month	Date	Active	Completed	Active	Active	Active	Completed	Completed	Completed
6	2023	Oct	0	7	Oct	10/01/2023	0	1	0	0	0	0	1	0
7	2023	Nov	1	20	Oct	10/02/2023	2	11	0	1	1	3	5	3
8	2023	Dec	0	21	Oct	10/03/2023	1	17	0	1	0	2	11	4
9	2024	Jan	0	0	Oct	10/04/2023	0	8	0	0	0	0	4	4
10	2024	Feb	0	0	Oct	10/05/2023	0	10	0	0	0	0	7	3
11	2024	Mar	0	0	Oct	10/06/2023	2	12	0	1	1	2	5	5
12	2024	Apr	0	0	Oct	10/07/2023	0	1	0	0	0	1	0	0
13	2023	Jun	2	12	Oct	10/08/2023	0	1	0	0	0	1	0	0
14					Oct	10/09/2023	0	4	0	0	0	2	1	1
15					Oct	10/10/2023	0	7	0	0	0	0	5	2
16					Oct	10/11/2023	1	20	0	1	0	0	14	6
17					Oct	10/12/2023	0	21	0	0	0	5	11	5
18					Oct	10/13/2023	0	7	0	0	0	3	2	2
Data-CM

Cell Formulas
Range	Formula
C6:C13	C6	=SUMIFS($G$6:$G$5000, $F$6:$F$5000,">="&DATEVALUE("01-"&B6&"-"&A6), $F$6:$F$5000,"<="& EOMONTH(DATEVALUE("01-"&B6&"-"&A6),0))
D6:D13	D6	=SUMIFS($H$6:$H$5000, $F$6:$F$5000,">="&DATEVALUE("01-"&B6&"-"&A6), $F$6:$F$5000,"<="& EOMONTH(DATEVALUE("01-"&B6&"-"&A6),0))