# YTD budget based on current input of actual

S

#### Sanj

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec YTD
actual 10 20 30 40 30 130
budget 15 15 20 25 20 25 35 20 25 30 35 15 .......

Need a formula in YTD budget row so that when I enter 10 in Jun actual (140
YTD actual) so that I can get 120 in YTD budget. Thanks for the help.
Sanj

B

#### Bernard Liengme

Labels (Jan , Feb ..) in B1:M12
Actual values in B2:M2
Budget values in B3:M3
=SUM(B3:M3)-SUMIF(B2:M2,"",B3:M3)
best wishes

S

#### Sanj

Thanks Bernard. It works pefectly!!!

Bernard Liengme said:
Labels (Jan , Feb ..) in B1:M12
Actual values in B2:M2
Budget values in B3:M3
=SUM(B3:M3)-SUMIF(B2:M2,"",B3:M3)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

J

#### JoeU2004

[My response to your duplicate posting in m.p.e.worksheet.functions. In the
future, it is better to post in only one m.p.excel NG.]

Sanj said:
Need a formula in YTD budget row so that when I enter 10 in Jun actual
(140 YTD actual) so that I can get 120 in YTD budget.

One way:

For actual YTD: =SUM(B1:M1)

For budget YTD: =SUMPRODUCT(--(B1:M1<>""),B2:M2)

----- original message -----

S

#### Sanj

Thanks for your help. Will definitely do in future.

JoeU2004 said:
[My response to your duplicate posting in m.p.e.worksheet.functions. In the
future, it is better to post in only one m.p.excel NG.]

Sanj said:
Need a formula in YTD budget row so that when I enter 10 in Jun actual
(140 YTD actual) so that I can get 120 in YTD budget.

One way:

For actual YTD: =SUM(B1:M1)

For budget YTD: =SUMPRODUCT(--(B1:M1<>""),B2:M2)

----- original message -----

Sanj said:
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec YTD
actual 10 20 30 40 30 130
budget 15 15 20 25 20 25 35 20 25 30 35 15 .......

Need a formula in YTD budget row so that when I enter 10 in Jun actual
(140
YTD actual) so that I can get 120 in YTD budget. Thanks for the help.
Sanj