Formula Calculation

Shadowboxer

Board Regular
Joined
Aug 24, 2006
Messages
60
I've tried and failed to construct a formula to do a reasonably straightforward calculation as follows:

Place total in cell B46

Total to be arrived at by adding the contents of the following cells:

F5 to F44
H5 to H44
J5 to J44
L5 to L44

It must be really simple but I'm rubbish at formulae.


Thanks
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
How flexible do you want this formula to be? Do you want a generic formula to sum up alternate columns?
 

Shadowboxer

Board Regular
Joined
Aug 24, 2006
Messages
60
How flexible do you want this formula to be? Do you want a generic formula to sum up alternate columns?

Hi Glenn,

I guess it's possible I may need to extend this at some future date so your answer will most likely come in handy.
 

Shadowboxer

Board Regular
Joined
Aug 24, 2006
Messages
60

ADVERTISEMENT

=SUM(F5:F44)+SUM(H5:H44)+SUM(J5:J44)+SUM(L5:L44)

That should work ok.

Thanks Artorius - it does indeed work just fine.

Cheers for the hand.

:biggrin:
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
OK, this will sum alternate columns ( even columns, as opposed to "odd" columns, so F, H, J & L ), and is an array formula ( entered using Ctrl-Shift-Enter instead of Enter ):
Code:
=SUM(IF(MOD(COLUMN(F5:M44),2)=0,F5:M44,0))
When you press Ctrl-Shift-Enter Excel will put curly brackets around your formula to indicate that it's an array formula.

As you can see, it is very easy to extend this formula to cover more columns, and can be switched to sum "odd" columns by changing the "=0" to be "=1". Remember to re-enter with Ctrl-Shift-Enter when altering the formula.
 

Shadowboxer

Board Regular
Joined
Aug 24, 2006
Messages
60
Thanks for all your answers guys - I'm going to have a play around with these additional answers to further my learning - thanks again! :)
 

Forum statistics

Threads
1,136,650
Messages
5,676,996
Members
419,667
Latest member
MegEri

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