YTD Budget Sum if Actual Month has activities



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

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!


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...
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
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.

Gary Brown

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question