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.

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.

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.

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.