| Monthly test.xlsx | ||||
|---|---|---|---|---|
| N | O | |||
| 1 | 1/11/2023 | 1/12/2023 | ||
| 2 | ||||
| 3 | Nov | Dec | ||
| 4 | $ 18,810.00 | $ 18,810.00 | ||
| 5 | $ 18,810.00 | |||
| 6 | $ - | |||
| 7 | 373.50 | 98.50 | ||
Monthly Invoicing | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| N1:O1 | N1 | =DATE(YEAR(N3),MONTH(N3),1) |
| N4:O4 | O4 | =SUMIF($B$24:$B$1017,O$3,$L$24:$L$112) |
| N5 | N5 | =SUMIFS($L$24:$L$1003,$B$24:$B$1003,N$3,$M$24:$M$1003,"y") |
| N6 | N6 | =SUMIFS($L$24:$L$1003,$B$24:$B$1003,N$3,$M$24:$M$1003,"") |
| N7:O7 | N7 | =SUMIF($B$24:$B$1018,N3,$D$24:$D$1013)+SUMIF($B$24:$B$1018,N3,$E$24:$E$1013) |
| Monthly test.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | |||
| 71 | Nov | THS NUFFIELD | 17.00 | 17.00 | $ 1,020.00 | $ 680.00 | $ - | $ - | $ 1,700.00 | y | ||||
| 72 | Nov | THS RUTHERFORD | 4.50 | 4.50 | $ 270.00 | $ 180.00 | $ - | $ - | $ 450.00 | y | ||||
| 73 | Nov | THS SIDEY SITEWORKS | 4.50 | 4.50 | $ 270.00 | $ 180.00 | $ - | $ - | $ 450.00 | y | ||||
| 74 | Dec | THS SIDEY LOT 4 | 12.00 | 8.00 | $ 720.00 | $ 320.00 | $ - | $ - | $ 1,040.00 | |||||
| 75 | Dec | THS MASSEY Lot 3 | 1.00 | $ 60.00 | $ - | $ - | $ - | $ 60.00 | ||||||
Monthly Invoicing | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D71:D75 | D71 | =IF($C71="","",SUMIFS(JAMESTIME,JAMESJOB,$C71,MONTH,$B71)) |
| E71:E75 | E71 | =IF($C71="","",SUMIFS(ONETIME,ONEJOB,$C71,MONTH,$B71)) |
| F71:F75 | F71 | =IF($C71="","",SUMIFS(TWOTIME,TWOJOB,$C71,MONTH,$B73)) |
| G71:G75 | G71 | =IF($C71="","",SUMIFS(THREETIME,THREEJOB,$C71,MONTH,$B73)) |
| H71:H75 | H71 | =IFERROR(SUMIF($O:$O,$C71,$P:$P)*D71,"") |
| I71:I75 | I71 | =IFERROR(SUMIF($O:$O,$C71,$Q:$Q)*E71,"") |
| J71:J75 | J71 | =IFERROR(SUMIF($O:$O,$C71,$R:$R)*F71,"") |
| K71:K75 | K71 | =IFERROR(SUMIF($O:$O,$C71,$S:$S)*G71,"") |
| L71:L75 | L71 | =SUM(H71:K71) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| B24:M53,B54:C54,D54:M60,C55:C60,B55:B93,C61:M88 | Cell Value | contains "$M10="y"" | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| C71:C75 | List | =$O$25:$O$100 |
