Sum columns

GrassHopper

New Member
Joined
Oct 17, 2002
Messages
9
I am trying to come up with a sum of certain cells in a row based on what is in the column.

Each row is a detail of hours, each column is a classification of those hours ie

columns REG OT OT1 OT2 LL
detail 1 .5 0 0 .25

what I want is sum of detail if columns = OT OT1 OT2.

I am able to create a simple sum of the cells that correspond with the appropriate columns, however if another column is added, then my total has the possibility of adding up the wrong cells.

I have tried a sumif, ie
=SUMIF(A2:A6,"OT""OT1""OT2",B2:B6)
but I end up with a 0 total

your help is much appreciated
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
On 2002-10-31 14:44, GrassHopper wrote:
I am trying to come up with a sum of certain cells in a row based on what is in the column.

Each row is a detail of hours, each column is a classification of those hours ie

columns REG OT OT1 OT2 LL
detail 1 .5 0 0 .25

what I want is sum of detail if columns = OT OT1 OT2.

I am able to create a simple sum of the cells that correspond with the appropriate columns, however if another column is added, then my total has the possibility of adding up the wrong cells.

I have tried a sumif, ie
=SUMIF(A2:A6,"OT""OT1""OT2",B2:B6)
but I end up with a 0 total

your help is much appreciated

Let A1:E2 house the sample you provided...

{"REG","OT","OT1","OT2","LL";1,0.5,0.2,0,0.25}

The formula

=SUMPRODUCT((SUMIF(A1:E1,{"OT","OT1","OT2"},A2:E2)))

will give you the desired sum of regarding OT, OT1, or OT2.
 
Upvote 0
....
=SUMPRODUCT((SUMIF(A1:E1,{"OT","OT1","OT2"},A2:E2)))

Hi Aladin,
Is there anyway to coerce this into something like
=SUMPRODUCT(((A1:E1)=({"OT","OT1","OT2"}))*A2:E2)
short of
=SUMPRODUCT(((A1:E1)=(myrange))*A2:E2)
where myrange contains the values? I assume the answer is no. Also interesting,
=SUM((SUMIF(A1:E1,{"OT","OT1","OT2"},A2:E2)))
appear to be identical (normally entered) to the sumproduct formulation.

I'll just add another option in case the array police are watching - consider using
=SUMIF(A1:E1,"ot*",A2:E2)

if no other Items starting with OT may be added to your header. Potential risk could be offset by improved calc times if using formulas in mass.
 
Upvote 0
Another twist, what if I wanted to say add those columns that don't equal REG?

would it be something like
=SUMPRPDUCT((SUMIf(A1:E1,not{"REG","LL"},A2:E2)))
 
Upvote 0
With only 1 criteria, you should be able to get away with a simple sumif,
=SUMIF(A1:E1,"<>REG",A2:E2)
 
Upvote 0
Is there anyway to coerce this into something like
=SUMPRODUCT(((A1:E1)=({"OT","OT1","OT2"}))*A2:E2)
short of
=SUMPRODUCT(((A1:E1)=(myrange))*A2:E2)
where myrange contains the values? I assume the answer is no.

Yes. Just take into account the fact that what we have here is a horizontal range... Thus:

=SUMPRODUCT((A1:E1=({"OT";"OT1";"OT2"}))*A2:E2)

Note semi-colon as separator in the array-constant instead of comma. So,

=SUMPRODUCT(((A1:E1)=(myrange))*A2:E2)

will not work without TRANSPOSE which necessitates array entry...

=SUMPRODUCT((A1:E1=TRANSPOSE(myrange))*(A2:E2))

Also interesting,
=SUM((SUMIF(A1:E1,{"OT","OT1","OT2"},A2:E2)))
appear to be identical (normally entered) to the sumproduct formulation.

Once again, we see SUM accept arbitrary objects and compute...

I'll just add another option in case the array police are watching - consider using
=SUMIF(A1:E1,"ot*",A2:E2)

if no other Items starting with OT may be added to your header. Potential risk could be offset by improved calc times if using formulas in mass.

Why not if conditions show such a regular structure!

Aladin
 
Upvote 0
Hey Aladin very nice to see the use of sumproduct sumif combination!! I was waiting to see how long it would take you to delve into it!!!!!!
 
Upvote 0
On 2002-10-31 17:21, planetpj wrote:
Hey Aladin very nice to see the use of sumproduct sumif combination!! I was waiting to see how long it would take you to delve into it!!!!!!

Note also Ian's (IML's) SUM+SUMIF observation.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top