SOLVED

Excel - Formula Needed to Sum YTD Totals in Cells Every 4 Columns Apart

New Contributor

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   September2022Number of Month
Item IncomeExpense  IncomeExpense  IncomeExpense  IncomeExpense  IncomeExpenses  YTDBudget9
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.001-Aug-22ü     1,200.001-Sep-22      1,200.001-Oct-22      1,200.001-Nov-22      1,200.001-Dec-22 ?   14,400.00  
Expense 2          250.0012-Aug-22ü         250.0012-Sep-22          250.0012-Oct-22          250.0012-Nov-22          250.0012-Dec-22 ?     3,000.00  
Expense 3          100.0025-Aug-22ü         100.0025-Sep-22          100.0025-Oct-22          100.0025-Nov-22          100.0025-Dec-22 ?     1,200.00  
      1,600.00    1,550.0050.00     1,600.00    1,550.0050.00     1,600.00    1,550.0050.00     1,600.00    1,550.0050.00     1,600.00    1,550.0050.00     
                          

 

Sample with Formulas Showing

Income and Expenses 44866   44896   =TEXT(TODAY(),"mmmm")=TEXT(TODAY(),"yyyy")Number of Month
Item IncomeExpense  IncomeExpenses  YTDBudget=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     
2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@jayvanjay 

 

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.