Hi. I have a spreadsheet set up as a calendar. I have 3 columns per date square: Amount, Description, and if cleared (checkmark). There are 5 rows in each square. The top 3 are for debits and the bottom two are for credits. The weekends are excluded. I would like to do a formula that searches across the 5 days, top 3 rows, and looks for an "x" in the "cleared" column, and if found, sum the amounts. If there are multiple debit entries that have cleared, all those amounts would be summed but not the ones without the "x". Any ideas on how to accomplish that?
2021BillsCalendar(New).xlsx | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
1 | July 2021 | |||||||||||||||||||||||
2 | ||||||||||||||||||||||||
3 | ||||||||||||||||||||||||
4 | ||||||||||||||||||||||||
5 | ||||||||||||||||||||||||
6 | ||||||||||||||||||||||||
7 | ||||||||||||||||||||||||
8 | Sum of Debits | Sum of Debts not cleared | ||||||||||||||||||||||
9 | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sum of Credits | Sum of Credits | |||||||||||||||
10 | Amt | Description | Ö | Amt | Description | Ö | Amt | Description | Ö | Amt | Description | Ö | Amt | Description | Ö | Difference | Difference | |||||||
11 | 27 | 28 | 29 | 30 | 1 | 2 | 3 | - | ||||||||||||||||
12 | - | |||||||||||||||||||||||
13 | - | |||||||||||||||||||||||
14 | ||||||||||||||||||||||||
15 | ||||||||||||||||||||||||
16 | ||||||||||||||||||||||||
17 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 212.50 | Should read 150 | |||||||||||||||
18 | 100 | Debit 1 | x | 63 | Debit 2 | 116.00 | Should read 116 | |||||||||||||||||
19 | 50 | Debit 3 | x | 328.50 | ||||||||||||||||||||
20 | ||||||||||||||||||||||||
21 | 48 | Credit 1 | ||||||||||||||||||||||
22 | 68 | Credit 2 | ||||||||||||||||||||||
23 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | - | ||||||||||||||||
24 | - | |||||||||||||||||||||||
25 | - | |||||||||||||||||||||||
26 | ||||||||||||||||||||||||
27 | ||||||||||||||||||||||||
28 | ||||||||||||||||||||||||
29 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | - | ||||||||||||||||
30 | - | |||||||||||||||||||||||
31 | - | |||||||||||||||||||||||
32 | ||||||||||||||||||||||||
33 | ||||||||||||||||||||||||
34 | ||||||||||||||||||||||||
35 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | - | ||||||||||||||||
36 | - | |||||||||||||||||||||||
37 | - | |||||||||||||||||||||||
38 | ||||||||||||||||||||||||
39 | ||||||||||||||||||||||||
40 | ||||||||||||||||||||||||
41 | 1 | 2 | Notes | - | ||||||||||||||||||||
42 | - | |||||||||||||||||||||||
43 | ||||||||||||||||||||||||
44 | ||||||||||||||||||||||||
45 | ||||||||||||||||||||||||
46 | ||||||||||||||||||||||||
Jul |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A1 | A1 | =DATE(DateEntry!C18,DateEntry!C20+6,1) |
A9,C9,F9,I9,L9,O9,R9 | A9 | =A11 |
A11 | A11 | =$A$1-(WEEKDAY($A$1,1)-(DateEntry!start_day-1))-IF((WEEKDAY($A$1,1)-(DateEntry!start_day-1))<=0,7,0)+1 |
C11,C41,C35,C29,C23,C17 | C11 | =A11+1 |
F11,R35,O35,L35,I35,F35,R29,O29,L29,I29,F29,R23,O23,L23,I23,F23,R17,O17,L17,I17,F17,R11,O11,L11,I11 | F11 | =C11+1 |
T11,T41,T35,T29,T23,T17 | T11 | =SUM(C12:Q14) |
T12,T42,T36,T30,T24,T18 | T12 | =SUM(C15:Q16) |
T13,T37,T31,T25,T19 | T13 | =T11+T12 |
A17,A41,A35,A29,A23 | A17 | =R11+1 |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
Print_Area | =Jul!$A$1:$S$46 | A11 |
DateEntry!start_day | =DateEntry!$C$24 | A11 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
O35 | Expression | =MONTH(O35)<>MONTH($A$1) | text | NO |
O35 | Expression | =OR(WEEKDAY(O35,1)=1,WEEKDAY(O35,1)=7) | text | NO |
O29 | Expression | =MONTH(O29)<>MONTH($A$1) | text | NO |
O29 | Expression | =OR(WEEKDAY(O29,1)=1,WEEKDAY(O29,1)=7) | text | NO |
O23 | Expression | =MONTH(O23)<>MONTH($A$1) | text | NO |
O23 | Expression | =OR(WEEKDAY(O23,1)=1,WEEKDAY(O23,1)=7) | text | NO |
O17 | Expression | =MONTH(O17)<>MONTH($A$1) | text | NO |
O17 | Expression | =OR(WEEKDAY(O17,1)=1,WEEKDAY(O17,1)=7) | text | NO |
O11 | Expression | =MONTH(O11)<>MONTH($A$1) | text | NO |
O11 | Expression | =OR(WEEKDAY(O11,1)=1,WEEKDAY(O11,1)=7) | text | NO |
L11 | Expression | =MONTH(L11)<>MONTH($A$1) | text | NO |
L11 | Expression | =OR(WEEKDAY(L11,1)=1,WEEKDAY(L11,1)=7) | text | NO |
L17 | Expression | =MONTH(L17)<>MONTH($A$1) | text | NO |
L17 | Expression | =OR(WEEKDAY(L17,1)=1,WEEKDAY(L17,1)=7) | text | NO |
L23 | Expression | =MONTH(L23)<>MONTH($A$1) | text | NO |
L23 | Expression | =OR(WEEKDAY(L23,1)=1,WEEKDAY(L23,1)=7) | text | NO |
L29 | Expression | =MONTH(L29)<>MONTH($A$1) | text | NO |
L29 | Expression | =OR(WEEKDAY(L29,1)=1,WEEKDAY(L29,1)=7) | text | NO |
L35 | Expression | =MONTH(L35)<>MONTH($A$1) | text | NO |
L35 | Expression | =OR(WEEKDAY(L35,1)=1,WEEKDAY(L35,1)=7) | text | NO |
I35 | Expression | =MONTH(I35)<>MONTH($A$1) | text | NO |
I35 | Expression | =OR(WEEKDAY(I35,1)=1,WEEKDAY(I35,1)=7) | text | NO |
I29 | Expression | =MONTH(I29)<>MONTH($A$1) | text | NO |
I29 | Expression | =OR(WEEKDAY(I29,1)=1,WEEKDAY(I29,1)=7) | text | NO |
I23 | Expression | =MONTH(I23)<>MONTH($A$1) | text | NO |
I23 | Expression | =OR(WEEKDAY(I23,1)=1,WEEKDAY(I23,1)=7) | text | NO |
I17 | Expression | =MONTH(I17)<>MONTH($A$1) | text | NO |
I17 | Expression | =OR(WEEKDAY(I17,1)=1,WEEKDAY(I17,1)=7) | text | NO |
I11 | Expression | =MONTH(I11)<>MONTH($A$1) | text | NO |
I11 | Expression | =OR(WEEKDAY(I11,1)=1,WEEKDAY(I11,1)=7) | text | NO |
F35 | Expression | =MONTH(F35)<>MONTH($A$1) | text | NO |
F35 | Expression | =OR(WEEKDAY(F35,1)=1,WEEKDAY(F35,1)=7) | text | NO |
F29 | Expression | =MONTH(F29)<>MONTH($A$1) | text | NO |
F29 | Expression | =OR(WEEKDAY(F29,1)=1,WEEKDAY(F29,1)=7) | text | NO |
F23 | Expression | =MONTH(F23)<>MONTH($A$1) | text | NO |
F23 | Expression | =OR(WEEKDAY(F23,1)=1,WEEKDAY(F23,1)=7) | text | NO |
F17 | Expression | =MONTH(F17)<>MONTH($A$1) | text | NO |
F17 | Expression | =OR(WEEKDAY(F17,1)=1,WEEKDAY(F17,1)=7) | text | NO |
F11 | Expression | =MONTH(F11)<>MONTH($A$1) | text | NO |
F11 | Expression | =OR(WEEKDAY(F11,1)=1,WEEKDAY(F11,1)=7) | text | NO |
C41 | Expression | =MONTH(C41)<>MONTH($A$1) | text | NO |
C41 | Expression | =OR(WEEKDAY(C41,1)=1,WEEKDAY(C41,1)=7) | text | NO |
C35 | Expression | =MONTH(C35)<>MONTH($A$1) | text | NO |
C35 | Expression | =OR(WEEKDAY(C35,1)=1,WEEKDAY(C35,1)=7) | text | NO |
C29 | Expression | =MONTH(C29)<>MONTH($A$1) | text | NO |
C29 | Expression | =OR(WEEKDAY(C29,1)=1,WEEKDAY(C29,1)=7) | text | NO |
C23 | Expression | =MONTH(C23)<>MONTH($A$1) | text | NO |
C23 | Expression | =OR(WEEKDAY(C23,1)=1,WEEKDAY(C23,1)=7) | text | NO |
C17 | Expression | =MONTH(C17)<>MONTH($A$1) | text | NO |
C17 | Expression | =OR(WEEKDAY(C17,1)=1,WEEKDAY(C17,1)=7) | text | NO |
R11,R17,R23,R29,R35 | Expression | =MONTH(R11)<>MONTH($A$1) | text | NO |
R11,R17,R23,R29,R35 | Expression | =OR(WEEKDAY(R11,1)=1,WEEKDAY(R11,1)=7) | text | NO |
A11,C11,A17,A23,A29,A35,A41 | Expression | =MONTH(A11)<>MONTH($A$1) | text | NO |
A11,C11,A17,A23,A29,A35,A41 | Expression | =OR(WEEKDAY(A11,1)=1,WEEKDAY(A11,1)=7) | text | NO |