[Excel] Sum of even cells in a column?
Danucci
-
jonniop -
jonniop -
Hello, I don't know much about all the features of Excel, but I would like to know how to calculate the sum of the even cells in a column between rows 4 and 204 for example (or every other row if you prefer).
Indeed, I can't list them all in the sum otherwise the function is too long.
So is there a formula such that I could choose the cell numbers by indicating that they are of the form 4+i*2 with i between 0 and 100, like in an algorithm?
And in general, is there a way to do this where I could put any formula instead of "4+i*2"?
Thanks to those who will help me. Bye!
Indeed, I can't list them all in the sum otherwise the function is too long.
So is there a formula such that I could choose the cell numbers by indicating that they are of the form 4+i*2 with i between 0 and 100, like in an algorithm?
And in general, is there a way to do this where I could put any formula instead of "4+i*2"?
Thanks to those who will help me. Bye!
7 réponses
Hello
How to calculate the sum of the even cells in a column between row 4 and 204 for example
To do this, you can use this formula:
=SUM((MOD(ROW(A4:A204),2)=0)*B4:B204)
Since this is an array formula, you need to validate it with Ctrl + Shift + Enter
This formula checks if the row number is even to include it in the sum.
To sum the odd rows, the formula is:
=SUM((MOD(ROW(A4:A204),2)<>0)*B4:B204)
I considered that the column to sum was column B,
if it is column D, you need to use D4:D204 of course, however,
for the row test, you can leave column A in all cases.
Is there a way to do this where I could replace "4+i*2" with any formula instead?
I don’t understand the question there, so if you want to explain it differently...
--
always zen
How to calculate the sum of the even cells in a column between row 4 and 204 for example
To do this, you can use this formula:
=SUM((MOD(ROW(A4:A204),2)=0)*B4:B204)
Since this is an array formula, you need to validate it with Ctrl + Shift + Enter
This formula checks if the row number is even to include it in the sum.
To sum the odd rows, the formula is:
=SUM((MOD(ROW(A4:A204),2)<>0)*B4:B204)
I considered that the column to sum was column B,
if it is column D, you need to use D4:D204 of course, however,
for the row test, you can leave column A in all cases.
Is there a way to do this where I could replace "4+i*2" with any formula instead?
I don’t understand the question there, so if you want to explain it differently...
--
always zen
Hello,
And to sum cells (always every other one) across the columns.
=SUM((MOD(ROW(A4:A204),2)<>0)*B4:B204)
becomes:
=SUM((MOD(ROW(A24:EQ24),2)<>0)*B25:EQ25)
I guess that won't work? :'(
Does anyone have an idea?
And to sum cells (always every other one) across the columns.
=SUM((MOD(ROW(A4:A204),2)<>0)*B4:B204)
becomes:
=SUM((MOD(ROW(A24:EQ24),2)<>0)*B25:EQ25)
I guess that won't work? :'(
Does anyone have an idea?
I don't know Excel because I work with OpenOffice, but I think it's pretty much the same thing.
What you can do is add 2 columns.
-> In the first column, first row, you enter 1 (or 0 if you don't want to count the value of this cell in your total). In the other rows, you enter =if(C1==1, 0, 1)
If you put this formula in column C and you've entered 1 (or 0) in cell C1, you will have a column of 0s and 1s alternating.
-> Then in the 2nd column, you enter =if(C1==1, B1, 0)
If the values you want to sum are in column B
Here’s what you will have:
B----------C----------D
12---------1---------12
2-----------0---------0
35---------1---------35
All you have to do is sum column D to get the total of one value out of two.
--
You must always anticipate the unexpected.
What you can do is add 2 columns.
-> In the first column, first row, you enter 1 (or 0 if you don't want to count the value of this cell in your total). In the other rows, you enter =if(C1==1, 0, 1)
If you put this formula in column C and you've entered 1 (or 0) in cell C1, you will have a column of 0s and 1s alternating.
-> Then in the 2nd column, you enter =if(C1==1, B1, 0)
If the values you want to sum are in column B
Here’s what you will have:
B----------C----------D
12---------1---------12
2-----------0---------0
35---------1---------35
All you have to do is sum column D to get the total of one value out of two.
--
You must always anticipate the unexpected.
Hello
I guess it probably doesn't work? :'(
If you had tested it, you would have seen that it works perfectly.
However, the usage is limited, because in order to be used, the data in the columns must be of identical nature.
If you have the items of the same order online, for example: in even rows the number of items, and in odd rows their amount.
This allows you to make the overall total in numbers and amounts.
--
always zen
I guess it probably doesn't work? :'(
If you had tested it, you would have seen that it works perfectly.
However, the usage is limited, because in order to be used, the data in the columns must be of identical nature.
If you have the items of the same order online, for example: in even rows the number of items, and in odd rows their amount.
This allows you to make the overall total in numbers and amounts.
--
always zen
Re,
Yes, I did try the command. It doesn't work; it returns a value of 0. (The values along the line are identical.)
Anyway, okay then.
Yes, I did try the command. It doesn't work; it returns a value of 0. (The values along the line are identical.)
Anyway, okay then.
Hello
Indeed, I understand what you want to do but your formula is not suitable.
This one:
is suitable for columns and sums the odd ones, but your arrays need to be the same size,
which means the columns should be identical, which is not the case with A24:EQ24 and B25:EQ25.
Thus, this gives the same result, because as you sum row 25, you can check the odd code of the column on any row.
--
stay calm
=SUM((MOD(ROW(A24:EQ24),2)<>0)*B25:EQ25)
Indeed, I understand what you want to do but your formula is not suitable.
This one:
=SUM((MOD(COLUMN(B25:EQ25),2)<>0)*B25:EQ25)
is suitable for columns and sums the odd ones, but your arrays need to be the same size,
which means the columns should be identical, which is not the case with A24:EQ24 and B25:EQ25.
Thus, this gives the same result, because as you sum row 25, you can check the odd code of the column on any row.
=SUM((MOD(COLUMN(B1:EQ1),2)<>0)*B25:EQ25)
--
stay calm