Hi

I am using this formula to sum colums in row f, between the range D3 to D4

sum(offset(F11,0,(D3)):offset(F11,0,(D4)))

All working worked well, then I had to insert another columns every other one,
along page and therefore my required data is in alternative cells starting
from the value in D3 and ending in the value in D4

How do i adjust, revamp, the formula to count the original values.

I have tried to look on the treads for sumproduct, but unable to manipulate
for my end result

Help would be appriciated

regards

Brian

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200711/1

Perhaps one of these:

This one sums alternating items in the range,
beginning with the 1st referenced value:
=SUMPRODUCT(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4))* (MOD(COLUMN(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4))) ,2)=MOD(COLUMN(OFFSET(F11,0,(D3))),2)))

This one sums range items that are in ODD numbered columns:
=SUMPRODUCT(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4))* (MOD(COLUMN(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4))) ,2))=1)

This one sums range items that are in EVEN numbered columns:
=SUMPRODUCT(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4))* (MOD(COLUMN(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4))) ,2))=0)

NOTE: Since text wrap will impact the display, there are NO spaces in those
formulas.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)







"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:7af3f35f4a300@uwe...
Hi

I am using this formula to sum colums in row f, between the range D3 to
D4

sum(offset(F11,0,(D3)):offset(F11,0,(D4)))

All working worked well, then I had to insert another columns every other
one,
along page and therefore my required data is in alternative cells starting
from the value in D3 and ending in the value in D4

How do i adjust, revamp, the formula to count the original values.

I have tried to look on the treads for sumproduct, but unable to
manipulate
for my end result

Help would be appriciated

regards

Brian

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200711/1

Hi Ron, thanks for the speedy reply

What I am attempting to do is the following:

My data is in row F12:F54. From cell F12 and every alternative cell to the
right is one form of values (vehicles), and in the other cells inbetween
another set of values(Revenue).
When the operator enters the starting week number in cell D3, and finishing
week in D4, then it will return the value of all vehicles values between
these weeks

Not sure now if i should be using the offset function?

regards


Ron Coderre wrote:
Perhaps one of these:

This one sums alternating items in the range,
beginning with the 1st referenced value:
=SUMPRODUCT(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4)) *(MOD(COLUMN(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4)) ),2)=MOD(COLUMN(OFFSET(F11,0,(D3))),2)))

This one sums range items that are in ODD numbered columns:
=SUMPRODUCT(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4)) *(MOD(COLUMN(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4)) ),2))=1)

This one sums range items that are in EVEN numbered columns:
=SUMPRODUCT(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4)) *(MOD(COLUMN(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4)) ),2))=0)

NOTE: Since text wrap will impact the display, there are NO spaces in those
formulas.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

Hi

[quoted text clipped - 19 lines]

Brian

--
Message posted via http://www.officekb.com

Try this:

=SUMPRODUCT((MOD((COLUMN(H11:V11)-7),2)=1)*((COLUMN(H11:V11)-7)=(D3*2-1))*((COLUMN(H11:V11)-7)<=(D4*2))*H12:V54)

Since text wrap will impact the display, here's that formula in sections:
=SUMPRODUCT((MOD((COLUMN(H11:V11)-7),2)=1)
*((COLUMN(H11:V11)-7)=(D3*2-1))
*((COLUMN(H11:V11)-7)<=(D4*2))*H12:V54)

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)



"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:7af5ba9e76ad8@uwe...
Hi Ron, thanks for the speedy reply

What I am attempting to do is the following:

My data is in row F12:F54. From cell F12 and every alternative cell to
the
right is one form of values (vehicles), and in the other cells inbetween
another set of values(Revenue).
When the operator enters the starting week number in cell D3, and
finishing
week in D4, then it will return the value of all vehicles values between
these weeks

Not sure now if i should be using the offset function?

regards


Ron Coderre wrote:
Perhaps one of these:

This one sums alternating items in the range,
beginning with the 1st referenced value:
=SUMPRODUCT(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4) )*(MOD(COLUMN(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4) )),2)=MOD(COLUMN(OFFSET(F11,0,(D3))),2)))

This one sums range items that are in ODD numbered columns:
=SUMPRODUCT(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4) )*(MOD(COLUMN(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4) )),2))=1)

This one sums range items that are in EVEN numbered columns:
=SUMPRODUCT(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4) )*(MOD(COLUMN(OFFSET(F11,0,(D3)):OFFSET(F11,0,(D4) )),2))=0)

NOTE: Since text wrap will impact the display, there are NO spaces in
those
formulas.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

Hi

[quoted text clipped - 19 lines]

Brian

--
Message posted via http://www.officekb.com

thanks for your help on this, I have gone back to rethinking the way the
sheet is set out

regards

Ron Coderre wrote:
Try this:

=SUMPRODUCT((MOD((COLUMN(H11:V11)-7),2)=1)*((COLUMN(H11:V11)-7)=(D3*2-1))*((COLUMN(H11:V11)-7)<=(D4*2))*H12:V54)

Since text wrap will impact the display, here's that formula in sections:
=SUMPRODUCT((MOD((COLUMN(H11:V11)-7),2)=1)
*((COLUMN(H11:V11)-7)=(D3*2-1))
*((COLUMN(H11:V11)-7)<=(D4*2))*H12:V54)

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

Hi Ron, thanks for the speedy reply

[quoted text clipped - 43 lines]

Brian

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200711/1