Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
YEAR TO DATE SUM
Working on a financial spreadsheet that adds columns to get a YTD total. The
columns are setup Actual, Budget, Actual, Budget, Actual, Budget etc. for the year. I can use the sum function for the actuals but wondered how to sum the budget columns to only include those months corresponding to the # of months I'm summing for Actuals to arrive at a YTD comparison. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
YEAR TO DATE SUM
Hi,
Suppose your data is setup like this in range A2:I4. In B1 and C1 enter the number 1. In D1, enter the following formula =b1+1 and copy till cell I1. In any cell, say O2, enter a number which depicts the month number till which you want to sum. For e.g., to sum till March, enter 3. Now in cell J4, enter the following formula = SUMPRODUCT(($B$3:$I$3=J$3)*(B1:I1<=$O$2),B4:I4) January February March April Budget Actual Budget Actual Budget Actual Budget Actual Reveue 100 101 102 103 104 105 106 107 Hope this helps. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Bri123321" wrote in message ... Working on a financial spreadsheet that adds columns to get a YTD total. The columns are setup Actual, Budget, Actual, Budget, Actual, Budget etc. for the year. I can use the sum function for the actuals but wondered how to sum the budget columns to only include those months corresponding to the # of months I'm summing for Actuals to arrive at a YTD comparison. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
YEAR TO DATE SUM
Thanks Ashish for responding. I tried to setup the example as explained
below but don't get a value... just 0. "Ashish Mathur" wrote: Hi, Suppose your data is setup like this in range A2:I4. In B1 and C1 enter the number 1. In D1, enter the following formula =b1+1 and copy till cell I1. In any cell, say O2, enter a number which depicts the month number till which you want to sum. For e.g., to sum till March, enter 3. Now in cell J4, enter the following formula = SUMPRODUCT(($B$3:$I$3=J$3)*(B1:I1<=$O$2),B4:I4) January February March April Budget Actual Budget Actual Budget Actual Budget Actual Reveue 100 101 102 103 104 105 106 107 Hope this helps. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Bri123321" wrote in message ... Working on a financial spreadsheet that adds columns to get a YTD total. The columns are setup Actual, Budget, Actual, Budget, Actual, Budget etc. for the year. I can use the sum function for the actuals but wondered how to sum the budget columns to only include those months corresponding to the # of months I'm summing for Actuals to arrive at a YTD comparison. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
YEAR TO DATE SUM
Hi,
It seems to works absolutely fine for me. Please feel free to contact me at if you face any difficulty. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Bri123321" wrote in message ... Thanks Ashish for responding. I tried to setup the example as explained below but don't get a value... just 0. "Ashish Mathur" wrote: Hi, Suppose your data is setup like this in range A2:I4. In B1 and C1 enter the number 1. In D1, enter the following formula =b1+1 and copy till cell I1. In any cell, say O2, enter a number which depicts the month number till which you want to sum. For e.g., to sum till March, enter 3. Now in cell J4, enter the following formula = SUMPRODUCT(($B$3:$I$3=J$3)*(B1:I1<=$O$2),B4:I4) January February March April Budget Actual Budget Actual Budget Actual Budget Actual Reveue 100 101 102 103 104 105 106 107 Hope this helps. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Bri123321" wrote in message ... Working on a financial spreadsheet that adds columns to get a YTD total. The columns are setup Actual, Budget, Actual, Budget, Actual, Budget etc. for the year. I can use the sum function for the actuals but wondered how to sum the budget columns to only include those months corresponding to the # of months I'm summing for Actuals to arrive at a YTD comparison. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting a year date into a specific day date | Charts and Charting in Excel | |||
Help w/formula to add 1 year to cell (date done) date due? | Excel Worksheet Functions | |||
pick up date, month and year from a date | Excel Discussion (Misc queries) | |||
Year-to-date year to date formula | Excel Worksheet Functions | |||
Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) |