Back to basic? How to sum?

  • Thread starter Thread starter gilbert
  • Start date Start date
G

gilbert

Gee...I know everyone would curious why I still don't know how to us
Sum function in view that I have been quite active in the past fe
months learning from all the Excel experts in this forum.... my proble
is if I want to sum alternate cells of a range, how can I do it th
fastest way, instead of having plus individual cells manually? Is ther
a way we could define to add certain cells or number of cells?

Please advise.....

Rgds,
Gilbert:
 
Create a non-contiguous range of the cells you wish to sum, and

=sum(MyNonContiguousRange)

Vaya con Dios,
Chuck, CABGx3
 
Hi gilbert

a couple of options for you
this will sum every second row between A1 & A30 starting at A1 (ie
A1+A3+A5....)
=SUMPRODUCT((MOD(ROW(A1:A30)-1,2)=0)*A1:A30)

this will sum every second row between A1 & A30 starting at A2(ie
A2+A4+A6....)
=SUMPRODUCT((MOD(ROW(A1:A30),2)=0)*A1:A30)

or if you want to add things up that isn't so neat, you can click on the
first cell to add, hold down the control key, click on the next cell etc ...
when you've got all the cells, click in the name box, give the range a name
(e.g. cellstoadd) and then in your formula use
=sum(cellstoadd)

Hope these help

Cheers
JulieD
 
Hi JulieD,

I supposed if I want to sum columns instead of rows, my formula woul
appear to be as follow:

=SUMPRODUCT((MOD(COLUMN(A1:E5)-1,2)=0)*A1:E5)?

but I get funny sumtotal using that...the sumtotal is larger than th
normal total.

Assuming the followings :-

A B C D E F G
1 1 2 3 4 5 sumproduct = 18?
2 1 2 3 4 5 normal sum = 15

Why is that so? I hope you could help to explain this.

Thank you..


Rgds,
Gilber
 
Are you sure you entered the formula exactly the way you have it in your
message? When I paste it into a sheet with your data, I (correctly) get
a result of 9.
 
Hi JE,

Yes...I tried once more and the result that I get is still 18 not 9
This time, I did not key in any formula, instead I copied what wa
posted into a new worksheet with the sample data of 1 to 5 in A1 to A
and B1 to B5 respectively.

Just wondering how you can get 9 when I get 18? Mind to email me you
worksheet? My email is (e-mail address removed)

Thank you.


Rgds,
Gilber
 
No need - when I entered it into my sheet, I didn't put the values in
row 2, and didn't notice that you'd had more than one row in your range.

Since you use A1:E5, you're summing 5 rows by 5 columns, not just row 1.

Try:

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

to return just the values from row 1
 
Could you possibly send me a copy of the spreadsheet as I cannot get the formula (sumproduct) to work when I type it in mysel

----- JE McGimpsey wrote: ----

No need - when I entered it into my sheet, I didn't put the values in
row 2, and didn't notice that you'd had more than one row in your range

Since you use A1:E5, you're summing 5 rows by 5 columns, not just row 1

Try

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

to return just the values from row
 
Back
Top