Sum A Named Range Across Multiple Columns

[centerNegative]

Here's another fun one that's sucked up days of my life with no
progress.

I have groups of columns representing the days of the week where
numbers representing amount of work done (just simple whole number
quantities) go. I have every other column named as Budget for the
budget of work to be done, and every other column beside those named
Actual, for actual work done.

So basically on this worksheet, columns A, C, E, G, I, K, etc... all
represent the named range Budget
(=Sheet2!$A:$A,Sheet2!$C:$C,Sheet2!$E:$E, etc. …), and columns B, D, F,
H, J represent the range Actual
(=Sheet2!$B:$B,Sheet2!$D:$D,Sheet2!$F:$F, etc. …). I want a separate
cell located elsewhere to sum up all the quantities in the Actual
columns, and another cell to separately sum up all the quantities for
the Budget columns. The trick to this, though, is I need only the
quantities summed up to be for the specific row of that sum
calculation.

That might not make the most sense so I’ll break it down again. I need
to sum all the quantities only in the columns labeled Actual for each
single row. It probably needs to a dynamic range/reference that
continues expanding to the right as long as there is a number value,
since the length of the sum calculations (i.e., number of days
involved) is open-ended.

I’ve tried combining a dynamic named range using the OFFSET command
with variations of SUMIF and SUMPRODUCT formulas and cannot get the
concept to work. I’ve also tried using a standard formula to sum up
every other column [ SUMPRODUCT(MOD(COLUMN(1:1),2)*1:1) ], which is
more what I need since the range of columns is indefinite, but the
problem there is I can’t get that formula to start calculating from the
middle of the sheet. It starts at the first column, summing them all,
and in actuality I need to start this formula around Column O or P,
because of preceding data and calculations. And the SUMIF formula
attempts I’ve made always sum up everything in the entire column and I
need to keep it contained to values just along the same row.

Any suggestions and assistance are always greatly appreciated! Thanks
in advance to all helping out!

 

[Bob Phillips]

The sum of budget for row 1 would be

=SUMPRODUCT(--(MOD(COLUMN(1:1),2)=1),1:1)

the actuals would be

=SUMPRODUCT(--(MOD(COLUMN(1:1),2)=0),1:1)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"centerNegative"

 

[centerNegative]

Bob, that works perfectly. Thanks very much for the help

 

[RagDyeR]

Since you said that you want to start at Column O or P, just include those
in your formula:

=SUMPRODUCT((MOD(COLUMN(O1:AM1),2)=1)*O1:AM1)

=SUMPRODUCT((MOD(COLUMN(P1:AL1),2)=0)*P1:AL1)

And of course, you can make these formulas as 'open-ended' as you wish, by
just expanding the ranges:

=SUMPRODUCT((MOD(COLUMN(P1:ES1),2)=1)*P1:ES1)

=SUMPRODUCT((MOD(COLUMN(P1:ET1),2)=0)*P1:ET1)


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"centerNegative"
message
Here's another fun one that's sucked up days of my life with no
progress.

I have groups of columns representing the days of the week where
numbers representing amount of work done (just simple whole number
quantities) go. I have every other column named as Budget for the
budget of work to be done, and every other column beside those named
Actual, for actual work done.

So basically on this worksheet, columns A, C, E, G, I, K, etc... all
represent the named range Budget
(=Sheet2!$A:$A,Sheet2!$C:$C,Sheet2!$E:$E, etc. …), and columns B, D, F,
H, J represent the range Actual
(=Sheet2!$B:$B,Sheet2!$D:$D,Sheet2!$F:$F, etc. …). I want a separate
cell located elsewhere to sum up all the quantities in the Actual
columns, and another cell to separately sum up all the quantities for
the Budget columns. The trick to this, though, is I need only the
quantities summed up to be for the specific row of that sum
calculation.

That might not make the most sense so I’ll break it down again. I need
to sum all the quantities only in the columns labeled Actual for each
single row. It probably needs to a dynamic range/reference that
continues expanding to the right as long as there is a number value,
since the length of the sum calculations (i.e., number of days
involved) is open-ended.

I’ve tried combining a dynamic named range using the OFFSET command
with variations of SUMIF and SUMPRODUCT formulas and cannot get the
concept to work. I’ve also tried using a standard formula to sum up
every other column [ SUMPRODUCT(MOD(COLUMN(1:1),2)*1:1) ], which is
more what I need since the range of columns is indefinite, but the
problem there is I can’t get that formula to start calculating from the
middle of the sheet. It starts at the first column, summing them all,
and in actuality I need to start this formula around Column O or P,
because of preceding data and calculations. And the SUMIF formula
attempts I’ve made always sum up everything in the entire column and I
need to keep it contained to values just along the same row.

Any suggestions and assistance are always greatly appreciated! Thanks
in advance to all helping out!