SUMIF based day of the week

[skell01]

I have a spreadsheet that has each day of the month in columns. I am trying to create a formula that identifies a column as Sunday and then adds data from cells for the Saturday through Monday prior to that Sunday. Dates are listed on the top row which identifies the day of the week. Specifically I want to add row 29 together for the previous week.

Master Daily Reconcile November.xlsx
	B	C	D	E	F	G	H	I
1	November 1, 2021	November 2, 2021	November 3, 2021	November 4, 2021	November 5, 2021	November 6, 2021	November 7, 2021	November 8, 2021
2	$386.46	$386.46	$386.46	$386.46	$386.46	$386.46		$386.46
3	$42.31	$42.31	$42.31	$42.31	$42.31	$42.31		$42.31
4	$5.58	$5.58	$5.58	$5.58	$5.58	$5.58		$5.58
5	$192.31	$192.31	$192.31	$192.31	$192.31	$192.31		$192.31
6						$454.00
7	$33.09	$33.09	$33.09	$33.09	$33.09	$33.09		$33.09
8	$266.67	$266.67	$266.67	$266.67	$266.67	$266.67		$266.67
9	$166.67	$166.67	$166.67	$166.67	$166.67	$166.67		$166.67
10	$842.00	$1,015.00	$1,023.00	$0.00	$0.00	$0.00		$0.00
11	$821.09
12	$171.73	$80.46	$158.64	$98.21	$111.20	$0.00		$0.00
13	$570.00
14	$52.99
15	$104.27	$93.72	$105.14	$124.66	$131.45	$0.00		$0.00
16
17	$44.47	$55.88	$165.05	$129.94	$93.90	$0.00		$0.00
18	$81.51	$70.48	$87.96	$92.16	$111.07	$0.00		$0.00
19	$3,781.15	$2,408.63	$2,632.87	$1,538.06	$1,540.70	$1,547.09		$1,093.09
20
21	$5,461.69	$2,570.40	$4,439.38	$3,477.46	$3,917.95	$0.00		$0.00
22	$325.22	$177.24	$1,036.88	$0.00	$0.00	$0.00		$0.00
23	$0.00	$45.06	$20.70	$12.61	$0.00	$0.00		$0.00
24	$76.12	$19.95	$61.12	$31.10	$0.00	$0.00		$0.00
25	$10.15	$19.06	$0.00	$7.17	$14.00	$0.00		$0.00
26
27
28	$5,873.18	$2,831.71	$5,558.08	$3,528.34	$3,931.95	$0.00		$0.00
29	$2,092.03	$423.08	$2,925.21	$1,990.28	$2,391.25	$1,547.09		$1,093.09
30
31	14.3%	35.8%	18.4%	0.0%	0.0%	0.0%		0.0%
32	24.6%	0.0%	0.0%	0.0%	0.0%	0.0%		0.0%
33	38.9%	35.8%	18.4%	0.0%	0.0%	0.0%		0.0%
34				$7,430.60
35				$7,430.60
Big Picture

Cell Formulas
Range	Formula
B1	B1	=DATE(YEAR(A1),MONTH(A1),DAY(A1))
C1	C1	=IF(A1="","",IF(MONTH(A1+1)<>MONTH(A1),"",A1+1))
D1:I1	D1	=IF(C1="","",IF(MONTH(C1+1)<>MONTH(C1),"",C1+1))
B2:I2	B2	=IF(WEEKDAY(B1)<>1,$AJ$1/($AH$1-$AH$2), " ")
B3:I3	B3	=IF(WEEKDAY(B1)<>1, $AJ$2/($AH$1-$AH$2), " ")
B4:I4	B4	=IF(WEEKDAY(B1)<>1,$AJ$3/($AH$1-$AH$2), " ")
B5:I5	B5	=IF(WEEKDAY(B1)<>1,$AJ$5/($AH$1-$AH$2), " ")
B6:I6	B6	=IF(WEEKDAY(B1)=7,(454),"")
B7:I7	B7	=IF(WEEKDAY(B1)<>1,$AJ$4/($AH$1-$AH$2), " ")
B8:I8	B8	=IF(WEEKDAY(B1)<>1,(266.67), "")
B9:I9	B9	=IF(WEEKDAY(B1)<>1,(166.67),"")
B12:I12	B12	=IF(WEEKDAY(B1)<>1,3%*(B28-B15-B17),"")
B15	B15	=IF(WEEKDAY(B1)<>1,'1stMstr'!$C$6, " ")
C15	C15	=IF(WEEKDAY(C1)<>1,'2ndMstr'!$C$6, " ")
D15	D15	=IF(WEEKDAY(D1)<>1,'3rdMstr'!$C$6, " ")
E15	E15	=IF(WEEKDAY(E1)<>1,'4thMstr'!$C$6, " ")
F15	F15	=IF(WEEKDAY(F1)<>1,'5thMstr'!$C$6, " ")
G15	G15	=IF(WEEKDAY(G1)<>1,'6thMstr'!$C$6, " ")
H15	H15	=IF(WEEKDAY(H1)<>1,'7thMstr'!$C$6, " ")
I15	I15	=IF(WEEKDAY(I1)<>1,'8thMstr'!$C$6, " ")
B17	B17	=IF(WEEKDAY(B1)<>1,'1stMstr'!$C$7, " ")
C17	C17	=IF(WEEKDAY(C1)<>1,'2ndMstr'!$C$7, " ")
D17	D17	=IF(WEEKDAY(D1)<>1,'3rdMstr'!$C$7, " ")
E17	E17	=IF(WEEKDAY(E1)<>1,'4thMstr'!$C$7, " ")
F17	F17	=IF(WEEKDAY(F1)<>1,'5thMstr'!$C$7, " ")
G17	G17	=IF(WEEKDAY(G1)<>1,'6thMstr'!$C$7, " ")
H17	H17	=IF(WEEKDAY(H1)<>1,'7thMstr'!$C$7, " ")
I17	I17	=IF(WEEKDAY(I1)<>1,'8thMstr'!$C$7, " ")
B18	B18	=IF(WEEKDAY(B1)<>1,'1stMstr'!$F$9, " ")
C18	C18	=IF(WEEKDAY(C1)<>1,'2ndMstr'!$F$9, " ")
D18	D18	=IF(WEEKDAY(D1)<>1,'3rdMstr'!$F$9, " ")
E18	E18	=IF(WEEKDAY(E1)<>1,'4thMstr'!$F$9, " ")
F18	F18	=IF(WEEKDAY(F1)<>1,'5thMstr'!$F$9, " ")
G18	G18	=IF(WEEKDAY(G1)<>1,'6thMstr'!$F$9, " ")
H18	H18	=IF(WEEKDAY(H1)<>1,'7thMstr'!$F$9, " ")
I18	I18	=IF(WEEKDAY(I1)<>1,'8thMstr'!$F$9, " ")
B19:I19	B19	=IF(WEEKDAY(B1)<>1,SUM(B2:B18),"")
B21	B21	=IF(WEEKDAY(B1)<>1,SUM('1stMstr'!$C$5+'1stMstr'!$C$6+'1stMstr'!$C$7),"")
C21	C21	=IF(WEEKDAY(C1)<>1,SUM('2ndMstr'!$C$5+'2ndMstr'!$C$6+'2ndMstr'!$C$7),"")
D21	D21	=IF(WEEKDAY(D1)<>1,SUM('3rdMstr'!$C$5+'3rdMstr'!$C$6+'3rdMstr'!$C$7),"")
E21	E21	=IF(WEEKDAY(E1)<>1,SUM('4thMstr'!$C$5+'4thMstr'!$C$6+'4thMstr'!$C$7),"")
F21	F21	=IF(WEEKDAY(F1)<>1,SUM('5thMstr'!$C$5+'5thMstr'!$C$6+'5thMstr'!$C$7),"")
G21	G21	=IF(WEEKDAY(G1)<>1,SUM('6thMstr'!$C$5+'6thMstr'!$C$6+'6thMstr'!$C$7),"")
H21	H21	=IF(WEEKDAY(H1)<>1,SUM('7thMstr'!$C$5+'7thMstr'!$C$6+'7thMstr'!$C$7),"")
I21	I21	=IF(WEEKDAY(I1)<>1,SUM('8thMstr'!$C$5+'8thMstr'!$C$6+'8thMstr'!$C$7),"")
B22	B22	=IF(WEEKDAY(B1)<>1,'1stMstr'!$I$18, " ")
C22	C22	=IF(WEEKDAY(C1)<>1,'2ndMstr'!$I$18, " ")
D22	D22	=IF(WEEKDAY(D1)<>1,'3rdMstr'!$I$18, " ")
E22	E22	=IF(WEEKDAY(E1)<>1,'4thMstr'!$I$18, " ")
F22	F22	=IF(WEEKDAY(F1)<>1,'5thMstr'!$I$18, " ")
G22	G22	=IF(WEEKDAY(G1)<>1,'6thMstr'!$I$18, " ")
H22	H22	=IF(WEEKDAY(H1)<>1,'7thMstr'!$I$18, " ")
I22	I22	=IF(WEEKDAY(I1)<>1,'8thMstr'!$I$18, " ")
B23	B23	=IF(WEEKDAY(B1)<>1,'1stMstr'!$I$19, " ")
C23	C23	=IF(WEEKDAY(C1)<>1,'2ndMstr'!$I$19, " ")
D23	D23	=IF(WEEKDAY(D1)<>1,'3rdMstr'!$I$19, " ")
E23	E23	=IF(WEEKDAY(E1)<>1,'4thMstr'!$I$19, " ")
F23	F23	=IF(WEEKDAY(F1)<>1,'5thMstr'!$I$19, " ")
G23	G23	=IF(WEEKDAY(G1)<>1,'6thMstr'!$I$19, " ")
H23	H23	=IF(WEEKDAY(H1)<>1,'7thMstr'!$I$19, " ")
I23	I23	=IF(WEEKDAY(I1)<>1,'8thMstr'!$I$19, " ")
B24	B24	=IF(WEEKDAY(B1)<>1,'1stMstr'!$I$20, " ")
C24	C24	=IF(WEEKDAY(C1)<>1,'2ndMstr'!$I$20, " ")
D24	D24	=IF(WEEKDAY(D1)<>1,'3rdMstr'!$I$20, " ")
E24	E24	=IF(WEEKDAY(E1)<>1,'4thMstr'!$I$20, " ")
F24	F24	=IF(WEEKDAY(F1)<>1,'5thMstr'!$I$20, " ")
G24	G24	=IF(WEEKDAY(G1)<>1,'6thMstr'!$I$20, " ")
H24	H24	=IF(WEEKDAY(H1)<>1,'7thMstr'!$I$20, " ")
I24	I24	=IF(WEEKDAY(I1)<>1,'8thMstr'!$I$20, " ")
B25	B25	=IF(WEEKDAY(B1)<>1,'1stMstr'!$I$22, " ")
C25	C25	=IF(WEEKDAY(C1)<>1,'2ndMstr'!$I$22, " ")
D25	D25	=IF(WEEKDAY(D1)<>1,'3rdMstr'!$I$22, " ")
E25	E25	=IF(WEEKDAY(E1)<>1,'4thMstr'!$I$22, " ")
F25	F25	=IF(WEEKDAY(F1)<>1,'5thMstr'!$I$22, " ")
G25	G25	=IF(WEEKDAY(G1)<>1,'6thMstr'!$I$22, " ")
H25	H25	=IF(WEEKDAY(H1)<>1,'7thMstr'!$I$22, " ")
I25	I25	=IF(WEEKDAY(I1)<>1,'8thMstr'!$I$22, " ")
B28:I28	B28	=IF(WEEKDAY(B1)<>1,SUM(B21:B26),"")
B29:I29	B29	=IF(WEEKDAY(B1)<>1,(B28-B19),"")
B31:I31	B31	=IF(WEEKDAY(B1)<>1,IF(B28 = 0, 0, B10/B28), " ")
B32:I32	B32	=IF(WEEKDAY(B1)<>1,IF(B28 = 0, 0, SUM(B11+B13+B14)/B28), " ")
B33:I33	B33	=IF(WEEKDAY(B1)<>1,SUM(B31:B32)," ")
E34	E34	=SUM(B29:E29)
E35	E35	=(E34)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E34:AE35	Cell Value	<0	text	NO
E34:AE35	Cell Value	>0	text	NO
B29:E29,G29:L29,N29:S29,U29:Z29,AB29:AE29	Cell Value	<0	text	NO
B29:E29,G29:L29,N29:S29,U29:Z29,AB29:AE29	Cell Value	>0	text	NO
B2:AF5,C6:E6,G6:AF6,B7:AF33	Expression	=WEEKDAY(B$1,2)>6	text	NO
B33:AF33	Cell Value	<0.6	text	NO
B33:AF33	Cell Value	>0.6	text	NO
B31:AF32	Cell Value	<0.3	text	NO
B31:AF32	Cell Value	>0.3	text	NO
B29:D30	Cell Value	<0	text	NO
B29:D30	Cell Value	>0	text	NO
A1:AF1	Cell	does not contain a blank value	text	NO

Thanks in advance.

 

[jasonb75]

First of all, what's with the long formulas in Row 1? All you need is =A1 in B1, then =B1+1 in C1 and dragged right.

That's just the first observation, I'm seeing a lot of inconsistency in your formulas which is making hard work of an easy job setting the sheet up to start with and will continue to make it harder when you try to analyse the results of those formulas. Using " " to show a blank is a bad idea, "" is the standard as it is comparable to an empty cell in many cases which makes things a lot easier.

For the main question, we would need to establish whether there is any consistency in the date pattern at the top. Will it always have a monday date in B1 or will that always be the 1st of the month? Does the data extend far enough right that there will be multiple sundays in the row? If so how do we know which sunday to look for?

There may be other details that need clarifying depending on your responses to the points above.

 

[skell01]

I am a novice, so I know my formulas are inconsistent and probably never the best method the get the information I am looking for, but they are what I was able to put together to make it work. I am reattaching to include the first column, I missed it before. A1 is a list of months. When I change that month, it then adjusts the top row accordingly. I know how to run a bakery, Excel, not so much. The blue columns are Sundays, when we are closed. What I want to happen is have a formula in each cell of row 34 and if that cell falls on a Sunday, add up the cells for Saturday back to Monday from row 29.

I hope this makes sense.

Master Daily Reconcile November.xlsx
	A	B	C	D	E	F	G	H
1	November 2021	November 1, 2021	November 2, 2021	November 3, 2021	November 4, 2021	November 5, 2021	November 6, 2021	November 7, 2021
2	Lease Per Business Day	$386.46	$386.46	$386.46	$386.46	$386.46	$386.46
3	XCEL Per Business Day	$42.31	$42.31	$42.31	$42.31	$42.31	$42.31
4	Xfinity Per Business Day	$5.58	$5.58	$5.58	$5.58	$5.58	$5.58
5	SBA Per Business Day	$192.31	$192.31	$192.31	$192.31	$192.31	$192.31
6	Farmers Market						$454.00
7	Steve And Penni Promisary	$33.09	$33.09	$33.09	$33.09	$33.09	$33.09
8	Bakemark	$266.67	$266.67	$266.67	$266.67	$266.67	$266.67
9	Sysco	$166.67	$166.67	$166.67	$166.67	$166.67	$166.67
10	Payroll Per Day	$842.00	$1,015.00	$1,023.00	$0.00	$0.00	$0.00
11	Inventory	$821.09
12	Royalty	$171.73	$80.46	$158.64	$98.21	$111.20	$0.00
13	Marketing	$570.00
14	Misc	$52.99
15	Tax	$104.27	$93.72	$105.14	$124.66	$131.45	$0.00
16	Owner Draw
17	Tips	$44.47	$55.88	$165.05	$129.94	$93.90	$0.00
18	Square Fees	$81.51	$70.48	$87.96	$92.16	$111.07	$0.00
19	Total	$3,781.15	$2,408.63	$2,632.87	$1,538.06	$1,540.70	$1,547.09
20
21	In Store Sales Collected	$5,461.69	$2,570.40	$4,439.38	$3,477.46	$3,917.95	$0.00
22	EZCater	$325.22	$177.24	$1,036.88	$0.00	$0.00	$0.00
23	GrubHub	$0.00	$45.06	$20.70	$12.61	$0.00	$0.00
24	Uber	$76.12	$19.95	$61.12	$31.10	$0.00	$0.00
25	Door Dash	$10.15	$19.06	$0.00	$7.17	$14.00	$0.00
26	Farmers Market
27
28	Total	$5,873.18	$2,831.71	$5,558.08	$3,528.34	$3,931.95	$0.00
29	Daily Profit	$2,092.03	$423.08	$2,925.21	$1,990.28	$2,391.25	$1,547.09
30
31	Payroll %	14.3%	35.8%	18.4%	0.0%	0.0%	0.0%
32	COGS %	24.6%	0.0%	0.0%	0.0%	0.0%	0.0%
33	Big 2	38.9%	35.8%	18.4%	0.0%	0.0%	0.0%
34	Weekly Profit				$7,430.60
35	Month to Date Profit				$7,430.60
Big Picture

Cell Formulas
Range	Formula
B1	B1	=DATE(YEAR(A1),MONTH(A1),DAY(A1))
C1	C1	=IF(A1="","",IF(MONTH(A1+1)<>MONTH(A1),"",A1+1))
D1:H1	D1	=IF(C1="","",IF(MONTH(C1+1)<>MONTH(C1),"",C1+1))
B2:H2	B2	=IF(WEEKDAY(B1)<>1,$AJ$1/($AH$1-$AH$2), " ")
B3:H3	B3	=IF(WEEKDAY(B1)<>1, $AJ$2/($AH$1-$AH$2), " ")
B4:H4	B4	=IF(WEEKDAY(B1)<>1,$AJ$3/($AH$1-$AH$2), " ")
B5:H5	B5	=IF(WEEKDAY(B1)<>1,$AJ$5/($AH$1-$AH$2), " ")
B6:H6	B6	=IF(WEEKDAY(B1)=7,(454),"")
B7:H7	B7	=IF(WEEKDAY(B1)<>1,$AJ$4/($AH$1-$AH$2), " ")
B8:H8	B8	=IF(WEEKDAY(B1)<>1,(266.67), "")
B9:H9	B9	=IF(WEEKDAY(B1)<>1,(166.67),"")
B12:H12	B12	=IF(WEEKDAY(B1)<>1,3%*(B28-B15-B17),"")
B15	B15	=IF(WEEKDAY(B1)<>1,'1stMstr'!$C$6, " ")
C15	C15	=IF(WEEKDAY(C1)<>1,'2ndMstr'!$C$6, " ")
D15	D15	=IF(WEEKDAY(D1)<>1,'3rdMstr'!$C$6, " ")
E15	E15	=IF(WEEKDAY(E1)<>1,'4thMstr'!$C$6, " ")
F15	F15	=IF(WEEKDAY(F1)<>1,'5thMstr'!$C$6, " ")
G15	G15	=IF(WEEKDAY(G1)<>1,'6thMstr'!$C$6, " ")
H15	H15	=IF(WEEKDAY(H1)<>1,'7thMstr'!$C$6, " ")
B17	B17	=IF(WEEKDAY(B1)<>1,'1stMstr'!$C$7, " ")
C17	C17	=IF(WEEKDAY(C1)<>1,'2ndMstr'!$C$7, " ")
D17	D17	=IF(WEEKDAY(D1)<>1,'3rdMstr'!$C$7, " ")
E17	E17	=IF(WEEKDAY(E1)<>1,'4thMstr'!$C$7, " ")
F17	F17	=IF(WEEKDAY(F1)<>1,'5thMstr'!$C$7, " ")
G17	G17	=IF(WEEKDAY(G1)<>1,'6thMstr'!$C$7, " ")
H17	H17	=IF(WEEKDAY(H1)<>1,'7thMstr'!$C$7, " ")
B18	B18	=IF(WEEKDAY(B1)<>1,'1stMstr'!$F$9, " ")
C18	C18	=IF(WEEKDAY(C1)<>1,'2ndMstr'!$F$9, " ")
D18	D18	=IF(WEEKDAY(D1)<>1,'3rdMstr'!$F$9, " ")
E18	E18	=IF(WEEKDAY(E1)<>1,'4thMstr'!$F$9, " ")
F18	F18	=IF(WEEKDAY(F1)<>1,'5thMstr'!$F$9, " ")
G18	G18	=IF(WEEKDAY(G1)<>1,'6thMstr'!$F$9, " ")
H18	H18	=IF(WEEKDAY(H1)<>1,'7thMstr'!$F$9, " ")
B19:H19	B19	=IF(WEEKDAY(B1)<>1,SUM(B2:B18),"")
B21	B21	=IF(WEEKDAY(B1)<>1,SUM('1stMstr'!$C$5+'1stMstr'!$C$6+'1stMstr'!$C$7),"")
C21	C21	=IF(WEEKDAY(C1)<>1,SUM('2ndMstr'!$C$5+'2ndMstr'!$C$6+'2ndMstr'!$C$7),"")
D21	D21	=IF(WEEKDAY(D1)<>1,SUM('3rdMstr'!$C$5+'3rdMstr'!$C$6+'3rdMstr'!$C$7),"")
E21	E21	=IF(WEEKDAY(E1)<>1,SUM('4thMstr'!$C$5+'4thMstr'!$C$6+'4thMstr'!$C$7),"")
F21	F21	=IF(WEEKDAY(F1)<>1,SUM('5thMstr'!$C$5+'5thMstr'!$C$6+'5thMstr'!$C$7),"")
G21	G21	=IF(WEEKDAY(G1)<>1,SUM('6thMstr'!$C$5+'6thMstr'!$C$6+'6thMstr'!$C$7),"")
H21	H21	=IF(WEEKDAY(H1)<>1,SUM('7thMstr'!$C$5+'7thMstr'!$C$6+'7thMstr'!$C$7),"")
B22	B22	=IF(WEEKDAY(B1)<>1,'1stMstr'!$I$18, " ")
C22	C22	=IF(WEEKDAY(C1)<>1,'2ndMstr'!$I$18, " ")
D22	D22	=IF(WEEKDAY(D1)<>1,'3rdMstr'!$I$18, " ")
E22	E22	=IF(WEEKDAY(E1)<>1,'4thMstr'!$I$18, " ")
F22	F22	=IF(WEEKDAY(F1)<>1,'5thMstr'!$I$18, " ")
G22	G22	=IF(WEEKDAY(G1)<>1,'6thMstr'!$I$18, " ")
H22	H22	=IF(WEEKDAY(H1)<>1,'7thMstr'!$I$18, " ")
B23	B23	=IF(WEEKDAY(B1)<>1,'1stMstr'!$I$19, " ")
C23	C23	=IF(WEEKDAY(C1)<>1,'2ndMstr'!$I$19, " ")
D23	D23	=IF(WEEKDAY(D1)<>1,'3rdMstr'!$I$19, " ")
E23	E23	=IF(WEEKDAY(E1)<>1,'4thMstr'!$I$19, " ")
F23	F23	=IF(WEEKDAY(F1)<>1,'5thMstr'!$I$19, " ")
G23	G23	=IF(WEEKDAY(G1)<>1,'6thMstr'!$I$19, " ")
H23	H23	=IF(WEEKDAY(H1)<>1,'7thMstr'!$I$19, " ")
B24	B24	=IF(WEEKDAY(B1)<>1,'1stMstr'!$I$20, " ")
C24	C24	=IF(WEEKDAY(C1)<>1,'2ndMstr'!$I$20, " ")
D24	D24	=IF(WEEKDAY(D1)<>1,'3rdMstr'!$I$20, " ")
E24	E24	=IF(WEEKDAY(E1)<>1,'4thMstr'!$I$20, " ")
F24	F24	=IF(WEEKDAY(F1)<>1,'5thMstr'!$I$20, " ")
G24	G24	=IF(WEEKDAY(G1)<>1,'6thMstr'!$I$20, " ")
H24	H24	=IF(WEEKDAY(H1)<>1,'7thMstr'!$I$20, " ")
B25	B25	=IF(WEEKDAY(B1)<>1,'1stMstr'!$I$22, " ")
C25	C25	=IF(WEEKDAY(C1)<>1,'2ndMstr'!$I$22, " ")
D25	D25	=IF(WEEKDAY(D1)<>1,'3rdMstr'!$I$22, " ")
E25	E25	=IF(WEEKDAY(E1)<>1,'4thMstr'!$I$22, " ")
F25	F25	=IF(WEEKDAY(F1)<>1,'5thMstr'!$I$22, " ")
G25	G25	=IF(WEEKDAY(G1)<>1,'6thMstr'!$I$22, " ")
H25	H25	=IF(WEEKDAY(H1)<>1,'7thMstr'!$I$22, " ")
B28:H28	B28	=IF(WEEKDAY(B1)<>1,SUM(B21:B26),"")
B29:H29	B29	=IF(WEEKDAY(B1)<>1,(B28-B19),"")
B31:H31	B31	=IF(WEEKDAY(B1)<>1,IF(B28 = 0, 0, B10/B28), " ")
B32:H32	B32	=IF(WEEKDAY(B1)<>1,IF(B28 = 0, 0, SUM(B11+B13+B14)/B28), " ")
B33:H33	B33	=IF(WEEKDAY(B1)<>1,SUM(B31:B32)," ")
E34	E34	=SUM(B29:E29)
E35	E35	=(E34)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E34:AE35	Cell Value	<0	text	NO
E34:AE35	Cell Value	>0	text	NO
B29:E29,G29:L29,N29:S29,U29:Z29,AB29:AE29	Cell Value	<0	text	NO
B29:E29,G29:L29,N29:S29,U29:Z29,AB29:AE29	Cell Value	>0	text	NO
B2:AF5,C6:E6,G6:AF6,B7:AF33	Expression	=WEEKDAY(B$1,2)>6	text	NO
B33:AF33	Cell Value	<0.6	text	NO
B33:AF33	Cell Value	>0.6	text	NO
B31:AF32	Cell Value	<0.3	text	NO
B31:AF32	Cell Value	>0.3	text	NO
B29:D30	Cell Value	<0	text	NO
B29:D30	Cell Value	>0	text	NO
A1:AF1	Cell	does not contain a blank value	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A1	List	Jan-2021, Feb-2021, Mar-2021, Apr-2021, May-2021, Jun-2021, Jul-2021, Aug-2021, Sep-2021, Oct-2021, Nov-2021, Dec-2021

 

[jasonb75]

With regard to my comment about inconsistency making things difficult, I would strongly advise that you use find and replace (press Ctrl h in excel) to change " " to "" in all of your formulas by entering " " in the first box and "" in the second, then clicking the 'Replace all' button. That is the one thing that is likely to cause problems later, the others are just long winded ways of doing things that shouldn't affect anything else you do to the sheet in the future.

As far as the formula goes that you've asked for, the easiest way to do what you want would be to enter

=IF(WEEKDAY(H1)=1,IFERROR(SUM(B29:G29),SUM($B29:G29)),"")

into G34, then drag it left to B34 to fill the first week, and right as far as needed to fill the rest of the month.

The formula is set up so that it sums the 6 columns to the left of the formula. As it may be necessary to sum less than 6 columns, say when the first sunday falls on the 4th day of the month, the formula can not be set up correctly from column B as it will cause errors. By starting from the middle and dragging it both ways we can work around those errors.

 

[skell01]

Thank you for the fix, it is applied and thank you for the formula, it works perfectly!