sumif function with delayed application

[jbrown021286]

hours log.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Day	date	notes	RO	status	SUM	Daignosis	CODE	JOB	FLAG TIME	ADJUSTMENT	Column1
3	Monday	4/8/2024		6208383		5.4	micu keyless reciver and battery				5.4		don’t count	Waiting	incorect
4	Monday	4/8/2024		6208727			door glass						0	0	0
5	Monday	4/8/2024		6208727		2.0	rotors				2		Total	Not Verifyed	Verifyed
6	Monday	4/8/2024		6208727		0.2		cf	cabin filter	0.2			0.0	0	0
7	Monday	4/8/2024		6208727		5.0	timing cover resaeal				5
8													Day	Hours	Average
9													Monday	12.6
10													Tuesday	0
11													Wednesday	0
12													Thursday	0
13													Friday	0
14													Saturday	0
15
16
17
18
19
20
LOG

Cell Formulas
Range	Formula
B3:B20	B3	=IFERROR((TEXT(WEEKDAY($C3),"Dddd")),"")
C3:C20	C3	=IF([@RO]<>"",IF(C3="",TODAY(),C3),"")
J3:J20	J3	=IF(I3="","",INDEX($Y$3:$Y$91,MATCH(I3,$X$3:$X$91,0),1))
K3:K20	K3	=IF(J3="","",INDEX($Z$3:$Z$91,MATCH(I3,$X$3:$X$91,0),1))
N4	N4	=SUMIF(E:E,"N",F:F)+SUMIF(E:E,"cnf",F:F)
O4	O4	=SUMIF(E:E,"w",F:F)
P4	P4	=SUMIF(E:E,"x",F:F)
N6	N6	=SUM(F:F,)-SUMIF(E:E,"N",F:F)-SUMIF(E:E,"nf",F:F)-O2
O6	O6	=SUMIF(E:E,"",F:F)+SUMIF(E:E,"c",F:F)
P6	P6	=SUMIF(E:E,"G",F:F)-O2
O9:O14	O9	=SUMIF($B$3:$B$280,N9,$G$3:$G$280)
G3:G20	G3	=SUMIF(Table111[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table111[@[FLAG TIME]:[ADJUSTMENT]])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F3:F280	Cell Value	=$N$39	text	NO
H5:H7	Cell Value	=$N$38	text	NO
H5:H7	Cell Value	=$N$37	text	NO
H5:H7	Cell Value	=$N$36	text	NO
H5:H7	Cell Value	=$N$35	text	NO
H5:H7	Cell Value	=$N$34	text	NO
H5:H7	Cell Value	=$N$33	text	NO
H19	Cell Value	=$N$38	text	NO
H19	Cell Value	=$N$37	text	NO
H19	Cell Value	=$N$36	text	NO
H19	Cell Value	=$N$35	text	NO
H19	Cell Value	=$N$34	text	NO
H19	Cell Value	=$N$33	text	NO
F3:F280	Cell Value	=$N$38	text	NO
F3:G280	Cell Value	=$N$37	text	NO
F3:G280	Cell Value	=$N$36	text	NO
F3:G280	Cell Value	=$N$35	text	NO
F3:G280	Cell Value	=$N$34	text	NO
F3:G280	Cell Value	=$N$33	text	NO
B3:C279	Expression	=" =COUNTIFS(D:D,A1,E:E,J2)>0"	text	NO
B3:C279	Expression	=COUNTIFS(J:J,XEY4,#REF!,B3)>0	text	NO
E3:E280,L22:L52,O36,O83	Expression	=COUNTIF(T:T,E3)>0	text	NO
G3:G280	Expression	=" =COUNTIFS(D:D,A1,E:E,J2)>0"	text	NO
G3:G280	Expression	=COUNTIFS(T:T,E3,U:U,G3)>0	text	NO
L3:M5,V3:V280,M6:M7	Expression	=COUNTIFS(XEX:XEX,K3,XEZ:XEZ,L3)>0	text	NO

is there a way to modify the formulas in O9 - O14 so that they don't sum matching days till the next day?

 

[kvsrinivasamurthy]

It is not clear what is required. Anyway try in O9 and copy down.

=SUMIF($B$3:$B$280,N9,$G$3:$G$280)+Sum($O$8:$O8)

 

[myall_blues]

I think what you want is doable, but before we do that you will need to make some changes to your spreadsheet and formulas. In particular, the formulas in C3:C20 are circular references i.e. the formula in C3 refers to itself. This is poor practice and may result in errors further along. In addition those same cells are using the volatile function TODAY(), meaning they will likely update the next time you open the sheet.
Are these data being entered manually?

 

[jbrown021286]

hours log.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2		Day	date	notes	RO	status	SUM	Daignosis	CODE	JOB	FLAG TIME	ADJUSTMENT	Column1								R.O.	Hours
3		Monday	4/8/2024		6208383	g	0.4					0.4		don’t count	Waiting	incorect					6208383	0.4
4		Monday	4/8/2024		6208383	g	1.0					1		0	0	0			Check List		6208383	1
5		Monday	4/8/2024		6208383	g	2.0					2		Total	Not Verifyed	Verifyed			14.6		6208383	2
6		Monday	4/8/2024		6208383	g	3.0	micu keyless reciver and battery				3		24.2	9.6	14.6					6208383	3
7		Monday	4/8/2024		6208767	g	1.0	door glass				1									6208767	1
8		Tuesday	4/9/2024		6208788		0.7		pro	propack	0.7			Day	Hours	Average					6208927	0
9		Tuesday	4/9/2024		6208788		1.4		pdi	pdi	1.4			Monday	14.6						6208927	0
10		Tuesday	4/9/2024		6208792		0.7		pro	propack	0.7			Tuesday	9.6						6208927	0
11		Tuesday	4/9/2024		6208792		1.4		pdi	pdi	1.4			Wednesday	0						6208927	0.2
12		Tuesday	4/9/2024		6208793		0.7		pro	propack	0.7			Thursday	0						6208927	2
13		Tuesday	4/9/2024		6208793		1.4		pdi	pdi	1.4			Friday	0						6208927	5
14		Monday	4/8/2024		6208927	g						0		Saturday	0
15		Monday	4/8/2024		6208927	g						0
16		Monday	4/8/2024		6208927	g						0
17		Monday	4/8/2024		6208927	g
18		Monday	4/8/2024		6208927	g	0.2		cf	cabin filter	0.2
19		Monday	4/8/2024		6208927	g	2.0	rotors				2
20		Monday	4/8/2024		6208927	g	5.0	timing cover resaeal				5
21		Tuesday	4/9/2024		6209023		2.5	valve adj				2.5
22		Tuesday	4/9/2024		6209023		0.8		bs	Brake Service	0.8
23
24
25
26
27														X	Incorrect
28														G	Verifyed
29														W	Waiting
30														N	Don’t Count
31														c	Need to check
32														nf	closed not flaged
33														wtf	Wrong tech flaged
34
35
36
Log

Cell Formulas
Range	Formula
J3:J36	J3	=IF(I3="","",INDEX($Y$3:$Y$91,MATCH(I3,$X$3:$X$91,0),1))
K3:K36	K3	=IF(J3="","",INDEX($Z$3:$Z$91,MATCH(I3,$X$3:$X$91,0),1))
N4	N4	=SUMIF(F:F,"N",G:G)+SUMIF(F:F,"cnf",G:G)
O4	O4	=SUMIF(F:F,"w",G:G)
P4	P4	=SUMIF(F:F,"x",G:G)
S5	S5	=SUM(V:V)
N6	N6	=SUM(G:G,)-SUMIF(F:F,"N",G:G)-SUMIF(F:F,"nf",G:G)-O2
O6	O6	=SUMIF(F:F,"",G:G)+SUMIF(F:F,"c",G:G)
P6	P6	=SUMIF(F:F,"G",G:G)-O2
C8:C13,C21:C36	C8	=IF([@RO]>0,TODAY(),"")
O9	O9	=SUMIF($B$3:$B$280,N9,$G$3:$G$280)+SUM($O$8:$O8)
O10:O14	O10	=SUMIF($B$3:$B$280,N10,$G$3:$G$280)
B3:B36	B3	=IFERROR(TEXT(WEEKDAY($C3),"Dddd"),"")
G3:G36	G3	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O30	Expression	=COUNTIF(AE:AE,O30)>0	text	NO
L22:L52,E3:E280	Expression	=COUNTIF(U:U,E3)>0	text	NO
C3	Expression	=" =COUNTIFS(D:D,A1,E:E,J2)>0"	text	NO
F3:F280	Cell Value	=$N$33	text	NO
H5:H7	Cell Value	=$N$32	text	NO
H5:H7	Cell Value	=$N$31	text	NO
H5:H7	Cell Value	=$N$30	text	NO
H5:H7	Cell Value	=$N$29	text	NO
H5:H7	Cell Value	=$N$28	text	NO
H5:H7	Cell Value	=$N$27	text	NO
H19	Cell Value	=$N$32	text	NO
H19	Cell Value	=$N$31	text	NO
H19	Cell Value	=$N$30	text	NO
H19	Cell Value	=$N$29	text	NO
H19	Cell Value	=$N$28	text	NO
H19	Cell Value	=$N$27	text	NO
F3:F280	Cell Value	=$N$32	text	NO
F3:G280	Cell Value	=$N$31	text	NO
F3:G280	Cell Value	=$N$30	text	NO
F3:G280	Cell Value	=$N$29	text	NO
F3:G280	Cell Value	=$N$28	text	NO
F3:G280	Cell Value	=$N$27	text	NO
U3:U280,E28:E37	Expression	=COUNTIF(XES:XES,E3)>0	text	NO
G3:G280	Expression	=" =COUNTIFS(D:D,A1,E:E,J2)>0"	text	NO
L3:M5,V3:V280,M6:M7	Expression	=COUNTIFS(XEY:XEY,K3,XFA:XFA,L3)>0	text	NO
G3:G280	Expression	=COUNTIFS(U:U,E3,V:V,G3)>0	text	NO
C3	Expression	=COUNTIFS(L:L,XEZ4,#REF!,C3)>0	text	NO

.
here is a better table. as i have everything right now collum g sums into column o if the days match in column b to column n. what i am wanting to do is not have the current day start to sum
in this example O10 Tuesday until the next day Wednesday

 

[jbrown021286]

I think what you want is doable, but before we do that you will need to make some changes to your spreadsheet and formulas. In particular, the formulas in C3:C20 are circular references i.e. the formula in C3 refers to itself. This is poor practice and may result in errors further along. In addition those same cells are using the volatile function TODAY(), meaning they will likely update the next time you open the sheet.
Are these data being entered manually?

the today functions act as a time stamp when anything is entered into column e. the circular reference is to lock that date in so it doesn't change i added another table example to the thread aswell where i had a couple of issues with my layout. any cells in column c that don't have a formula in the where dates entered manually

 

[jbrown021286]

hours log.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	4/10/2024
2		Day	date	notes	RO	status	SUM	Daignosis	CODE	JOB	FLAG TIME	ADJUSTMENT	Column1
3		Monday	4/8/2024		6208383	g	0.4					0.4		don’t count	Waiting	incorect
4		Monday	4/8/2024		6208383	g	1.0					1		0	0	0
5		Monday	4/8/2024		6208383	g	2.0					2		Total	Not Verifyed	Verifyed
6		Monday	4/8/2024		6208383	g	3.0	micu keyless reciver and battery				3		25.2	10.6	14.6
7		Monday	4/8/2024		6208767	g	1.0	door glass				1
8		Tuesday	4/9/2024		6208788		0.7		pro	propack	0.7			Day	Hours	Average
9		Tuesday	4/9/2024		6208788		1.4		pdi	pdi	1.4			Monday	14.6
10		Tuesday	4/9/2024		6208792		0.7		pro	propack	0.7			Tuesday	10.6
11		Tuesday	4/9/2024		6208792		1.4		pdi	pdi	1.4			Wednesday	0
12		Tuesday	4/9/2024		6208793		0.7		pro	propack	0.7			Thursday	0
13		Tuesday	4/9/2024		6208793		1.4		pdi	pdi	1.4			Friday	0
14		Monday	4/8/2024		6208927	g						0		Saturday	0
15		Monday	4/8/2024		6208927	g						0
16		Monday	4/8/2024		6208927	g						0
17		Monday	4/8/2024		6208927	g
18		Monday	4/8/2024		6208927	g	0.2		cf	cabin filter	0.2
19		Monday	4/8/2024		6208927	g	2.0	rotors				2
20		Monday	4/8/2024		6208927	g	5.0	timing cover resaeal				5
21		Tuesday	4/9/2024		6209023		2.5	valve adj				2.5
22		Tuesday	4/9/2024		6209023		0.8		bs	Brake Service	0.8
23		Tuesday	4/9/2024		6209027		1.0	door glass				1
24
25
26
Log

Cell Formulas
Range	Formula
A1	A1	=TODAY()
J3:J26	J3	=IF(I3="","",INDEX($Y$3:$Y$91,MATCH(I3,$X$3:$X$91,0),1))
K3:K26	K3	=IF(J3="","",INDEX($Z$3:$Z$91,MATCH(I3,$X$3:$X$91,0),1))
N4	N4	=SUMIF(F:F,"N",G:G)+SUMIF(F:F,"cnf",G:G)
O4	O4	=SUMIF(F:F,"w",G:G)
P4	P4	=SUMIF(F:F,"x",G:G)
N6	N6	=SUM(G:G,)-SUMIF(F:F,"N",G:G)-SUMIF(F:F,"nf",G:G)-O2
O6	O6	=SUMIF(F:F,"",G:G)+SUMIF(F:F,"c",G:G)
P6	P6	=SUMIF(F:F,"G",G:G)-O2
O9:O14	O9	=SUMIFS($G$3:$G$280,$B$3:$B$280,N9,$C$3:$C$280,"<"&A$1)
B3:B26	B3	=IFERROR(TEXT(WEEKDAY($C3),"Dddd"),"")
C24:C26	C24	=IF([@RO]<>"",IF(C24="",TODAY(),C24),"")
G3:G26	G3	=SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:E280,L22:L52	Expression	=COUNTIF(U:U,E3)>0	text	NO
C3	Expression	=" =COUNTIFS(D:D,A1,E:E,J2)>0"	text	NO
F3:F280	Cell Value	=$N$33	text	NO
H5:H7	Cell Value	=$N$32	text	NO
H5:H7	Cell Value	=$N$31	text	NO
H5:H7	Cell Value	=$N$30	text	NO
H5:H7	Cell Value	=$N$29	text	NO
H5:H7	Cell Value	=$N$28	text	NO
H5:H7	Cell Value	=$N$27	text	NO
H19	Cell Value	=$N$32	text	NO
H19	Cell Value	=$N$31	text	NO
H19	Cell Value	=$N$30	text	NO
H19	Cell Value	=$N$29	text	NO
H19	Cell Value	=$N$28	text	NO
H19	Cell Value	=$N$27	text	NO
F3:F280	Cell Value	=$N$32	text	NO
F3:G280	Cell Value	=$N$31	text	NO
F3:G280	Cell Value	=$N$30	text	NO
F3:G280	Cell Value	=$N$29	text	NO
F3:G280	Cell Value	=$N$28	text	NO
F3:G280	Cell Value	=$N$27	text	NO
G3:G280	Expression	=" =COUNTIFS(D:D,A1,E:E,J2)>0"	text	NO
L3:M5,V3:V280,M6:M7	Expression	=COUNTIFS(XEY:XEY,K3,XFA:XFA,L3)>0	text	NO
G3:G280	Expression	=COUNTIFS(U:U,E3,V:V,G3)>0	text	NO
C3	Expression	=COUNTIFS(L:L,XEZ4,#REF!,C3)>0	text	NO

I FIGURED IT OUT BY ADDING A HELPER CELL WITH THE CURRENT DATE AND ADDING AN AGUMENT TO MY SUM IFS FUNCTION