How Accumulate the Values?

[gatarossi]

Dear all,

In my sales worksheet, in the last column I need to put a formula in
order to sum the values for each month, I mean, sometime I need the
accumulated value from jan to june or jan to may for example.

Then I need to put this formula (in case of line 2, but I have many
lines...)

If I have this situation, I need to put this formula:

Situation Formula
1~1 Accum B2
1~2 Accum B2+D2
1~3 Accum B2+D2+F2
1~4 Accum B2+D2+F2+H2
1~5 Accum B2+D2+F2+H2+J2
1~6 Accum B2+D2+F2+H2+J2+L2
...
...
...

How can I do this? I put a validation in the first cell of this column
in order I can choose what accumulated values I'm looking for at the
moment...

Thanks in advance!!!

André.

 

[Ragdyer]

If I understand what you're looking for, you have monthly data in every
second column, starting with January in Column B.
So, with data going from B to X (Jan. to Dec.), enter this formula in Y2:

=SUMPRODUCT((MOD(COLUMN(B:X),2)=0)*B2:X2)

And copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Dear all,

In my sales worksheet, in the last column I need to put a formula in
order to sum the values for each month, I mean, sometime I need the
accumulated value from jan to june or jan to may for example.

Then I need to put this formula (in case of line 2, but I have many
lines...)

If I have this situation, I need to put this formula:

Situation Formula
1~1 Accum B2
1~2 Accum B2+D2
1~3 Accum B2+D2+F2
1~4 Accum B2+D2+F2+H2
1~5 Accum B2+D2+F2+H2+J2
1~6 Accum B2+D2+F2+H2+J2+L2
....
....
....

How can I do this? I put a validation in the first cell of this column
in order I can choose what accumulated values I'm looking for at the
moment...

Thanks in advance!!!

André.

 

[Jim Cone]

Or this one in Y2 (filled down) and the desired month name entered in Z1...
=SUM(B2:OFFSET($A$1,ROW()-1,MATCH($Z$1,$A$1:$X$1,0)-1,1,1))
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



<[email protected]>
wrote in message
Dear all,
In my sales worksheet, in the last column I need to put a formula in
order to sum the values for each month, I mean, sometime I need the
accumulated value from jan to june or jan to may for example.
Then I need to put this formula (in case of line 2, but I have many
lines...)
If I have this situation, I need to put this formula:

Situation Formula
1~1 Accum B2
1~2 Accum B2+D2
1~3 Accum B2+D2+F2
1~4 Accum B2+D2+F2+H2
1~5 Accum B2+D2+F2+H2+J2
1~6 Accum B2+D2+F2+H2+J2+L2
....
....
How can I do this? I put a validation in the first cell of this column
in order I can choose what accumulated values I'm looking for at the
moment...
Thanks in advance!!!
André.

 

[gatarossi]

Dear Jim,

This function works, but in my sales worksheet I'm having a little
problem: this is my worksheet layout:

A B C
D E
Product January
February
Last Year Actual Year Last Year Actual
Year
TV 100 120
70 80

So this formula sum the last year and the actual year (togheter). This
formula is in the right way, but I need to sum only the columns with
last year figures and other that sum only actual year figures.

Dear Ragdyer

I need to choose until what month I need to do the sum, so I didn't
understand this function well.

Thanks in advance for all!!!!

 

[gatarossi]

Dear all

I solve this problem with this formula:

=SUMIF(OFFSET(R1C1,1,MATCH(R1C26,R1C1:R1C25,0)-1,1,1):OFFSET(R1C1,1,MATCH(R1C27,R1C1:R1C25,0)-1,1,1),""PY"",OFFSET(R1C1,ROW()-1,MATCH(R1C26,R1C1:R1C25,0)-1,1,1):OFFSET(R1C1,ROW()-1,MATCH(R1C27,R1C1:R1C25,0)-1,1,1))

Thanks in advance!!!

André

 

[Jim Cone]

Your data layout was very muddled up.
This formula seems to work, but explaining it or maintaining it will be difficult.
It sums the even numbered columns up to the month name entered in Z1.
To sum the odd numbered columns change "=0" to "<>0"

=SUMPRODUCT((MOD(COLUMN($B2:OFFSET($A$1,ROW()-1,MATCH($Z$1,$B$1:$X$1,0),1,1)),2)=0)*($B2:OFFSET($A$1,ROW()-1,MATCH($Z$1,$B$1:$X$1,0),1,1)))

A Vba function would be my preference.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



<[email protected]>
wrote in message
Dear Jim,
This function works, but in my sales worksheet I'm having a little
problem: this is my worksheet layout:

A B C
D E
Product January
February
Last Year Actual Year Last Year Actual
Year
TV 100 120
70 80

So this formula sum the last year and the actual year (togheter). This
formula is in the right way, but I need to sum only the columns with
last year figures and other that sum only actual year figures.

Dear Ragdyer

I need to choose until what month I need to do the sum, so I didn't
understand this function well.

Thanks in advance for all!!!!