what function or formula can I use

need some help on this,
ie budget spreadsheet
row is the variable, ie production tons
columns are months, Plan and actual, ie b3 is january Plan, c3 is january
actual
d3 is february plan and e3 is february actual, and so on for the remaining
months

\at the end of row 3, there is a "YTD plan" cell, say z3.

what formula would work so that it will sum up only the planned #'s for
months that have entered "actual" for those months
hence giving me the YTD plan to compare with my entered actuals
ie if I planned 10 for every month, it is now june, so I would have actual
numbers from jan to May. The YTD cell would only sum up the "planned" cells
for which actuals were entered. meaning the cell would sum up to equal 50
(the 5 months with actuals)

Hi Adam,
Is it possible to change the structure of your sheet slightly?
I suggest only 1 column for each month.
Month names in Row1
Planned quantities in Row 2
Actual quantities in Row 3
This will make summing and most other math much easier.
Regards - Dave.

"Adam" wrote:

need some help on this,
ie budget spreadsheet
row is the variable, ie production tons
columns are months, Plan and actual, ie b3 is january Plan, c3 is january
actual
d3 is february plan and e3 is february actual, and so on for the remaining
months

\at the end of row 3, there is a "YTD plan" cell, say z3.

what formula would work so that it will sum up only the planned #'s for
months that have entered "actual" for those months
hence giving me the YTD plan to compare with my entered actuals
ie if I planned 10 for every month, it is now june, so I would have actual
numbers from jan to May. The YTD cell would only sum up the "planned" cells
for which actuals were entered. meaning the cell would sum up to equal 50
(the 5 months with actuals)

Unfortunately I already posed that question to the owner and he would like
for it to remain the same. I filed him in on ranges etc and how the current
design of the spreadsheet really isnt efficient. Am I stuck, or is it still
possible
I appreciate the assistance dave

"Dave" wrote:

Hi Adam,
Is it possible to change the structure of your sheet slightly?
I suggest only 1 column for each month.
Month names in Row1
Planned quantities in Row 2
Actual quantities in Row 3
This will make summing and most other math much easier.
Regards - Dave.

"Adam" wrote:

need some help on this,
ie budget spreadsheet
row is the variable, ie production tons
columns are months, Plan and actual, ie b3 is january Plan, c3 is january
actual
d3 is february plan and e3 is february actual, and so on for the remaining
months

\at the end of row 3, there is a "YTD plan" cell, say z3.

what formula would work so that it will sum up only the planned #'s for
months that have entered "actual" for those months
hence giving me the YTD plan to compare with my entered actuals
ie if I planned 10 for every month, it is now june, so I would have actual
numbers from jan to May. The YTD cell would only sum up the "planned" cells
for which actuals were entered. meaning the cell would sum up to equal 50
(the 5 months with actuals)

Hi Adam,
I'm not quite clear on exactly what you want, but I'll give it a try.
I think your data is in B3:Y3
B3, D3, F3 ... X3 contain planned quantities
C3, E3, G3 ... Y3 contain actual quantities, or will do as time goes on.
To sum just the planned quantities:
=SUMPRODUCT((B3:Y3)*(MOD(COLUMN(B3:Y3),2)=0))

To sum just the actual quantities, regardless of how many there are,
=SUMPRODUCT((B3:Y3)*(MOD(COLUMN(B3:Y3),2)=1))

I have a feeling that you want a bit more than that. Feel free to ask again.
Regards - Dave

Hi Adam,
Perhaps this is what you want for the planned total cell.
It only adds the planned inputs that have a corresponding actual input.
=SUMPRODUCT((B3:Y3)*(MOD(COLUMN(B3:Y3),2)=0)*(ISNU MBER(C3:Z3)))
Note that I have had to use cell Z3 in the formula, because SUMPRODUCT needs
equal length lists to work with, and I needed to off set the last part of the
formula by one cell. It would be best if you didn't use Z3 for anything.
The running total for the actual quantities is as below.
Regards - Dave.


"Dave" wrote:

Hi Adam,
I'm not quite clear on exactly what you want, but I'll give it a try.
I think your data is in B3:Y3
B3, D3, F3 ... X3 contain planned quantities
C3, E3, G3 ... Y3 contain actual quantities, or will do as time goes on.
To sum just the planned quantities:
=SUMPRODUCT((B3:Y3)*(MOD(COLUMN(B3:Y3),2)=0))

To sum just the actual quantities, regardless of how many there are,
=SUMPRODUCT((B3:Y3)*(MOD(COLUMN(B3:Y3),2)=1))

I have a feeling that you want a bit more than that. Feel free to ask again.
Regards - Dave

Thanks Dave I'll give it a try and let you know

"Dave" wrote:

Hi Adam,
Perhaps this is what you want for the planned total cell.
It only adds the planned inputs that have a corresponding actual input.
=SUMPRODUCT((B3:Y3)*(MOD(COLUMN(B3:Y3),2)=0)*(ISNU MBER(C3:Z3)))
Note that I have had to use cell Z3 in the formula, because SUMPRODUCT needs
equal length lists to work with, and I needed to off set the last part of the
formula by one cell. It would be best if you didn't use Z3 for anything.
The running total for the actual quantities is as below.
Regards - Dave.


"Dave" wrote:

Hi Adam,
I'm not quite clear on exactly what you want, but I'll give it a try.
I think your data is in B3:Y3
B3, D3, F3 ... X3 contain planned quantities
C3, E3, G3 ... Y3 contain actual quantities, or will do as time goes on.
To sum just the planned quantities:
=SUMPRODUCT((B3:Y3)*(MOD(COLUMN(B3:Y3),2)=0))

To sum just the actual quantities, regardless of how many there are,
=SUMPRODUCT((B3:Y3)*(MOD(COLUMN(B3:Y3),2)=1))

I have a feeling that you want a bit more than that. Feel free to ask again.
Regards - Dave

Thanks dave, it seems to work well, much appreciated

"Dave" wrote:

Hi Adam,
Perhaps this is what you want for the planned total cell.
It only adds the planned inputs that have a corresponding actual input.
=SUMPRODUCT((B3:Y3)*(MOD(COLUMN(B3:Y3),2)=0)*(ISNU MBER(C3:Z3)))
Note that I have had to use cell Z3 in the formula, because SUMPRODUCT needs
equal length lists to work with, and I needed to off set the last part of the
formula by one cell. It would be best if you didn't use Z3 for anything.
The running total for the actual quantities is as below.
Regards - Dave.


"Dave" wrote:

Hi Adam,
I'm not quite clear on exactly what you want, but I'll give it a try.
I think your data is in B3:Y3
B3, D3, F3 ... X3 contain planned quantities
C3, E3, G3 ... Y3 contain actual quantities, or will do as time goes on.
To sum just the planned quantities:
=SUMPRODUCT((B3:Y3)*(MOD(COLUMN(B3:Y3),2)=0))

To sum just the actual quantities, regardless of how many there are,
=SUMPRODUCT((B3:Y3)*(MOD(COLUMN(B3:Y3),2)=1))

I have a feeling that you want a bit more than that. Feel free to ask again.
Regards - Dave