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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

GrassHopper

New Member
Joined
Oct 17, 2002
Messages
9
You are the Man!!!
Thanks for the help
I tried using sumproduct before but had it backwards.
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
....
=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.
 

GrassHopper

New Member
Joined
Oct 17, 2002
Messages
9

ADVERTISEMENT

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)))
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
With only 1 criteria, you should be able to get away with a simple sumif,
=SUMIF(A1:E1,"<>REG",A2:E2)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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
 

planetpj

Active Member
Joined
Jun 25, 2002
Messages
347
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!!!!!!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,143,615
Messages
5,719,728
Members
422,242
Latest member
hishamkhatri

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
Top