Excel Sum Question?

I'm trying to track a calendar year metric in relation to a planned goal. I can easily set up the metric to read SUM A3:L3 and it will show me the current YTD value at the present month in a cell at the end of the data (ie: cell I3).

The goal is already on the chart for all 21 categories. I need only 2 of these categories to track to goal by adding the current and past month amounts only. For example, if my goals for Jan, Feb, March and April are 500, 750, 1000 and 1500 respectively, and it's only February, I need the YTD Goal to show 1250 (500+750). When March rolls around, I need it to show 2250 (500+750+1000).

I've thought of inserting an extra row that contains "Actl" and "Fcst" lines, but can't think of a way to set up the formula so it reads the goals in only the "Actl" columns--and only in the two categories I need it for.

I might be better off doing this manually, but wanted to take a shot and see if anyone knew how to set this up to perform automatically.

Excel Sum Question?shows

This will entail a long formula.

This yahoo site will not accept it.

I just wrote it and it won't "Submit" all of it.

Question. Which edition of Excel do you have?

How many characters will it accept in a single formula?

They should accept 250, and my 2003 should, but will not. Can't figure.

Excel Sum Question?comedy show opera theater

i dont really understand your question. but this formula might point you in the right direction. What it does is this:

IT says that if the month in the current month is the same as the month in cell A1, it will be true.

so you can do something like this

=IF(MONTH(A1) = MONTH(NOW()), xx, "")

Just put what ever calculation you want in where the xx is.

so maybe like

=IF(MONTH(A1) = MONTH(NOW()), sum(B1:B3), "") or something like that?...

sorry i cant be of more help %26gt;.%26lt;
Here is a way you can set it up to be automatic. It's actually pretty simple, but since I don't know the exact layout of your spreadsheet it's going to take me a lot of words and assumptions to explain it. You can take a blank worksheet and work with my examples and then apply it to your project.

Let's assume that in row 1 of Columns A through L you have a header for each month. A1 = Jan, B1 = Feb, etc.

Let's assume that in row 2 you have the monthly goals.

And let's say in cell M2 is where you want the YTD goal to be automatically calculated, based on the current real-time month.

To set this up, I am going to ask you to insert 2 rows above the month headers.

Once you have inserted 2 rows above the month headers, the month headers are in Row 3, and the monthly goals are in Row 4.

In row 2 (above the text version of the months) put the numeric equivalent of each month. So above Jan put 1, above Feb put 2, above Mar put 3, etc.

In row 1 put the following formula in cell A1

= IF ( MONTH (TODAY ( ) ) %26gt;= B1 , 1 ,0 )

Then drag copy it across row 1 above the other 11 months.

This formula is like an automatic "on / off" switch for each month. If the current month is March, then you will have 1's above Jan, Feb, and Mar, and zeroes above the other 9 months. These switches will be used by the "automatic" formula to calculate the YTD goal.

The place for the YTD goal value should be cell M4. (was originally in M2, but we inserted 2 rows).

So in M4, enter this formula:

=SUMIF ( A1:L1 , 1, A4:L4 )

This formula basically adds up only those numbers in Cells A4 through L4 that had a number 1 above it in row 1.

This should do it for you.