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.
Thanks in advance.
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.