alternative cells

[BNT1 via OfficeKB.com]

Hi

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

sum(offset(F11,0,(D3))ffset(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

 

[Ron Coderre]

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]

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

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

 

[Ron Coderre]

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

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

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

 

[BNT1 via OfficeKB.com]

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

regards

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]