YTD Budget Sum if Actual Month has activities

[AGnes]

My worksheet has 50 rows showing sales for each book. It has 39 columns - 3
for each month - budget, actual and diff - and 3 more columns for YTD. They
are like this:
Jan-Budget Jan-Actual Jan-Diff Feb-Budget Feb-Actual Feb-Diff
etc.

I have budgets for each month for each book on the worksheet already. I
update it monthly to enter actual sales for the month. I want the YTD-Budget
column to only sum up months that have actual activities so I can do a fair
comparison with YTD-Actual.

I tried to create a formula that sums up the budget columns for months where
Total Actual for the month is <> 0. But I didnt' get it right.

I'd appreciate if you can help me with that. Thanks in advance!

 

[Gary Brown]

Let's assume...
Your data is in columns A thru AM [the first 39 columns in a worksheet].
Your YTD Budget is in Column AK.
Your data begins on row 2.
----------------------------------
(1) In Columns AO thru AZ, create YTD Budget columns.
i.e.: AO is YTD Jan thru Jan, AP is YTD JAn thru Feb...AZ is Jan thru Dec

(2) On a monthly basis, put the Column letter in AN1.
i.e.: In Jan enter AO in cell AN1, In Feb enter AP in cell AN1, In Dec
enter AZ in AN1.

(3) In your YTD Budget column - [cell AK2], put the following formula...
=INDIRECT($AN$1&ROW())
Copy this down your 50 rows.
----------------------------------
What the formula does...
It looks at the column you've indicated in cell 'AN1' (case insensitive).
It then looks at the row that the formula is on and puts the 2 pieces of
information together. The Indirect function then looks at that information
which looks like a cell address to it and grabs the value that's in that cell
address.
i.e.: If you put AW in cell AN1 and your formula is on row 17, the indirect
function will return the value in cell AW17.

HTH,
Gary Brown