SOLVED

New Contributor

# 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
2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

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

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.

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

Thanks for the suggestion.