Sum IFs linked to Dates

[lisa_swinton]

Please see below mini sheet I'm trying to create the correct formula so that in the summary sheet row 11 can generate the next month in a sequence but then in the line 13 summaries data from the "data" sheet column U linked to the Date Column M. But it seems that in the data sheet I would need to have the exact same date as the Summary sheet but this wont be the case. Is there a way to summaries the data only from the MMM-YY and it not require exact date. Appreciate some pointers

Formula help.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	CSA Date	30-Sep-22
2	Remaining TC	500
3	No. Months	10
4
5		10	9	8	7	6	5	4	3	2	1	0	-1	-2
6	Include?	1	1	1	1	1	1	1	1	1	1	1	1	1
7	Month	9	10	11	12	1	2	3	4	5	6	7	8	9
8	Year	2022	2022	2022	2022	2023	2023	2023	2023	2023	2023	2023	2023	2023
9
10
11		Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23
12	TC at start of period	500	130	100	100	100	100	100	100	100	100	100	100	100
13	Risk Impact during period	-370	-30	0	0	0	0	0	0	0	0	0	0	0
14	Remaining TC	130	100	100	100	100	100	100	100	100	100	100	100	100
15
16	Exp Cost per Month	-370	-30	0	0	0	0	0	0	0	0	0	0	0
17
summary

Cell Formulas
Range	Formula
B1	B1	=data!W2
B2	B2	=data!N10
B5	B5	=B3
C5:N5	C5	=B5-B6
B7	B7	=MONTH(B1)
C7:N7	C7	=IF(C6=1, IF(B7+1=13, 1, B7+1), 0)
B8	B8	=YEAR(B1)
C8:N8	C8	=IF(C6=1, IF(B7=12, B8+1, B8), 0)
B11:B12	B11	=B1
C11:N11	C11	=DATE(YEAR(B11),MONTH(B11)+1,DAY(B11))
C12:N12	C12	=IF(C6=1, B14, "")
B13	B13	=SUMIF(data!$M$15:$M$74, B11, data!$U$15:$U$74)
C13	C13	=IF(C6=1,SUMIF(data!$M$15:$M$74,C11,data!$U$15:$U$74), "")
D13:N13	D13	=IF(D6=1,SUMIF(data!$M$15:$M$74,summary!D11,data!$U$15:$U$74), "")
B14	B14	=B12+B13
C14:N14	C14	=IF(C6=1, C12+C13, "")
B16:N16	B16	=IF(B6=1, B13, "")

Formula help.xlsx
	D	E				I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
2																			CSA Date:	Sep-22
3
4
5
6
7
8
9
10							TC	3,000	3,000		500
11
12
13								Threat Assessment		Exposure Period			Worst Case (in k)			Achievable (in k)			Best Case (in k)
14	Risk Nature	Description Risk				Id Risk	Reason	Trend Risk	Current Risk Rating	Start Date Risk	End Date Risk	Exposure Qtr	Rev	Cost	GM Impact	Rev	Cost	GM Impact	Rev	Cost	GM Impact
15	Threat	Test 1				1	HSE incident	→	4	Sep-22	Oct-22	Q3-2022		600	-600		500	-500		500	-500
16	Threat	Test 2				2	HSE incident	↗	1	Sep-22	Oct-22	Q3-2022		20	-20		10	-10	100	50	50
17	Threat	Test 3				3	HSE incident	→	256	Oct-22	Nov-22	Q4-2022		40	-40		30	-30		10	-10
18	Opportunity	Test 4				4	HSE incident	N	256	Sep-22	Nov-22	Q3-2022	50	10	40	100	10	90	100		100
19	Opportunity	Test 5				5	HSE incident	→	16	Sep-22	Nov-22	Q3-2022	50	10	40	60	10	50
20	Opportunity	Test 6				6	HSE incident	↘	8	Nov-22	Nov-22	Q4-2022	20	10	10	20	10	10
21																				-50	50
22																				-50	50
23
data

Cell Formulas
Range	Formula
M10	M10	=Z75
P13	P13	="Worst Case (in k)"
S13	S13	="Achievable (in k)"
V13	V13	="Best Case (in k)"
D15:D23	D15	=IF(ISBLANK($E15)," ",IFS($U15>0,"Opportunity",AND(R15>=0,U15=0,X15>0),"Opportunity",U15="","",$U15<=0,"Threat"))
O15:O23	O15	=IF(ISBLANK(M15),"",("Q"&ROUNDUP(MONTH(M15)/3,0)&"-"&YEAR(M15)))
R15:R23,X15:X23,U15:U23	R15	=P15-Q15

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L15:L74	Expression	=AND($D15="Threat",$L15>0,$L15<9)	text	NO
M10	Cell Value	<0	text	NO
N10	Cell Value	<0	text	NO
L15:L74	Expression	=AND($D15="Threat",OR($L15=16,$L15=32))	text	NO
L15:L74	Expression	=AND($D15="Opportunity", $L15>=64,$L15<=256)	text	NO
L15:L74	Expression	=AND($D15="Opportunity",OR($L15=16,$L15=32))	text	NO
L15:L74	Expression	=AND($D15="Opportunity",$L15>1,$L15<9)	text	NO
L15:L74	Expression	=AND($D$15="Threat",OR($L15=64,,$L15=128,$L15=256))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
W2	Custom	=IF(DAY(W2+1)=1,TRUE,FALSE)
J15:J74	List	=#REF!$A$2:$A$30
K15:K74	List	=$N$80:$N$84
L15:L74	List	=$M$80:$M$88
M15:M19	Custom	=M15>$W$2
N15:N74	Custom	=N15>=M15
O15:O74	Custom	=AND(N15>$R$2,OR(AND(MONTH(O15)>MONTH(N15),O15>N15),AND(MONTH(O15)<=MONTH(N15),YEAR(O15)>YEAR(N15))))
M20:M74	Any value

 

[kevin9999]

From what I can see, the values in cells B13 and C13 are correct with the formulas you're already using. What do you believe is wrong with those values?

 

[lisa_swinton]

From what I can see, the values in cells B13 and C13 are correct with the formulas you're already using. What do you believe is wrong with those values?

Hi Kevin the problem comes if in the data sheet Column M the Exposure start date if this is not the 30th of each month then the values aren't summed in the Summary sheet. So I was trying to find if there was a way only to sum using the MMM-YY rather than it using the DD as well.

 

[Skovgaard]

Try this in B13:

=SUMIFS(data!$U$15:$U$20,data!$M$15:$M$20,"<="&EOMONTH(Summary!B11,0);data!$M$15:$M$20,">="&EOMONTH(B11,-1)+1)

/Skovgaard

 

[kevin9999]

Hi Kevin the problem comes if in the data sheet Column M the Exposure start date if this is not the 30th of each month then the values aren't summed in the Summary sheet. So I was trying to find if there was a way only to sum using the MMM-YY rather than it using the DD as well.

Understood The formula suggested by @Skovgaard will give you what you want - just watch that semi-colon before the last data! (should be a comma )

 

[Skovgaard]

Understood The formula suggested by @Skovgaard will give you what you want - just watch that semi-colon before the last data! (should be a comma )

Thanks Kevin, not always easy to correct from semicolon to comma

Below is corrected:

=SUMIFS(data!$U$15:$U$20,data!$M$15:$M$20,"<="&EOMONTH(Summary!B11,0),data!$M$15:$M$20,">="&EOMONTH(B11,-1)+1)

/Skovgaard

 

[lisa_swinton]

Try this in B13:

=SUMIFS(data!$U$15:$U$20,data!$M$15:$M$20,"<="&EOMONTH(Summary!B11,0);data!$M$15:$M$20,">="&EOMONTH(B11,-1)+1)

/Skovgaard

Thanks that's done it save me some time trying to figure it out