Formula Calculation

[Shadowboxer]

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

 

[GlennUK]

How flexible do you want this formula to be? Do you want a generic formula to sum up alternate columns?

 

[Artorius]

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

That should work ok.

 

[Shadowboxer]

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]

=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.

 

[GlennUK]

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

=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.

 

[Krishnakumar]

Hi,

=SUMPRODUCT(--(MOD(COLUMN(F5:L44)-COLUMN(F5)+0,2)=0)*(F5:L44))

HTH

 

[Shadowboxer]

Thanks for all your answers guys - I'm going to have a play around with these additional answers to further my learning - thanks again!