Forum Discussion
Excel - Formula Needed to Sum YTD Totals in Cells Every 4 Columns Apart
For the following budget spreadsheet, I'm able to sum a yearly (budgeted) total for each Income Item and each Expense Item. What I'd like to do is also sum a year-to-date (actual) total.
Thanks for your help.
Sample
| Income and Expenses | 1-Aug-22 | 1-Sep-22 | 1-Oct-22 | 1-Nov-22 | 1-Dec-22 | September | 2022 | Number of Month | |||||||||||||||||
| Item | Income | Expense | Income | Expense | Income | Expense | Income | Expense | Income | Expenses | YTD | Budget | 9 | ||||||||||||
| Income A | 1,000.00 | 1-Aug-22 | ü | 1,000.00 | 1-Sep-22 | 1,000.00 | 1-Oct-22 | 1,000.00 | 1-Nov-22 | 1,000.00 | 1-Dec-22 | ? | 12,000.00 | ||||||||||||
| Income B | 500.00 | 10-Aug-22 | ü | 500.00 | 10-Sep-22 | 500.00 | 10-Oct-22 | 500.00 | 10-Nov-22 | 500.00 | 10-Dec-22 | ? | 6,000.00 | ||||||||||||
| Income C | 100.00 | 15-Aug-22 | ü | 100.00 | 15-Sep-22 | 100.00 | 15-Oct-22 | 100.00 | 15-Nov-22 | 100.00 | 15-Dec-22 | ? | 1,200.00 | ||||||||||||
| Expense 1 | 1,200.00 | 1-Aug-22 | ü | 1,200.00 | 1-Sep-22 | 1,200.00 | 1-Oct-22 | 1,200.00 | 1-Nov-22 | 1,200.00 | 1-Dec-22 | ? | 14,400.00 | ||||||||||||
| Expense 2 | 250.00 | 12-Aug-22 | ü | 250.00 | 12-Sep-22 | 250.00 | 12-Oct-22 | 250.00 | 12-Nov-22 | 250.00 | 12-Dec-22 | ? | 3,000.00 | ||||||||||||
| Expense 3 | 100.00 | 25-Aug-22 | ü | 100.00 | 25-Sep-22 | 100.00 | 25-Oct-22 | 100.00 | 25-Nov-22 | 100.00 | 25-Dec-22 | ? | 1,200.00 | ||||||||||||
| 1,600.00 | 1,550.00 | 50.00 | 1,600.00 | 1,550.00 | 50.00 | 1,600.00 | 1,550.00 | 50.00 | 1,600.00 | 1,550.00 | 50.00 | 1,600.00 | 1,550.00 | 50.00 | |||||||||||
Sample with Formulas Showing
| Income and Expenses | 44866 | 44896 | =TEXT(TODAY(),"mmmm") | =TEXT(TODAY(),"yyyy") | Number of Month | ||||||||
| Item | Income | Expense | Income | Expenses | YTD | Budget | =MONTH(TODAY()) | ||||||
| Income A | =AM3 | =AQ$1+0 | =AQ3 | =AU$1+0 | =SUM(IF(MOD(COLUMN(C3:AU3),4)=3,C3:AU3)) | ||||||||
| Income B | =AM4 | =AQ$1+9 | =AQ4 | =AU$1+9 | =SUM(IF(MOD(COLUMN(C4:AU4),4)=3,C4:AU4)) | ||||||||
| Income C | =AM5 | =AQ$1+14 | =AQ5 | =AU$1+14 | =SUM(IF(MOD(COLUMN(C5:AU5),4)=3,C5:AU5)) | ||||||||
| Expense 1 | =AN6 | =AQ$1+0 | =AR6 | =AU$1+0 | =SUM(IF(MOD(COLUMN(D6:AV6),4)=0,D6:AV6)) | ||||||||
| Expense 2 | =AN7 | =AQ$1+11 | =AR7 | =AU$1+11 | =SUM(IF(MOD(COLUMN(D7:AV7),4)=0,D7:AV7)) | ||||||||
| Expense 3 | =AN8 | =AQ$1+24 | =AR8 | =AU$1+24 | =SUM(IF(MOD(COLUMN(D8:AV8),4)=0,D8:AV8)) | ||||||||
| =SUM(AQ3:AQ8) | =SUM(AR3:AR8) | =AQ9-AR9 | =SUM(AU3:AU8) | =SUM(AV3:AV8) | =AU9-AV9 |
I'm going to suggest you change your approach altogether. Excel has a marvelous tool called the Pivot Table. Using it--I've attached an example--you can let Excel do all the heavy lifting of summarizing totals for income and expense, doing so by month and by income and expense categories. All you need to do is enter the raw data for each cash flow event.
Here's a good source for learning how the Pivot Table works. You'll also find a great array of videos on YouTube.
2 Replies
I'm going to suggest you change your approach altogether. Excel has a marvelous tool called the Pivot Table. Using it--I've attached an example--you can let Excel do all the heavy lifting of summarizing totals for income and expense, doing so by month and by income and expense categories. All you need to do is enter the raw data for each cash flow event.
Here's a good source for learning how the Pivot Table works. You'll also find a great array of videos on YouTube.
- Thanks for the suggestion.
