Adding all even number row

J

jeanulrich00

Hi

I have a file containing many worksheets. As each worksheet have the
same patern I am trying to find a formula that I could use for every
worksheet.

For each worksheet, the column are the same but the number of row can
be different.

Let say I have 10 columns A to J and I have 20 rows starting at row
10. and finishing at row 30

My first need was to be able to sum every even number low for each
column

example D10 + D12 + D14 + D16 + D18 and so on till row 30 Then I need
another formula to sum every odd number row D11 + D13 + D15 + D17 and
so on. As the number of row is not the same I went with DSUM
fonction.

As I understand well the DSUM function I am trying to find a solution
for that :

I need a formula that can replace this (D10*B10) + (D12*B12) +
(D14*B14) + (D16*B16) and so on

I need a formula that will work if I have 20 rows or if I have 100
rows

thanks for helping
 
M

macropod

Ji jean,

To sum every 2nd row in the range $A$1:$A$10, use the following formula:
=SUM(IF(MOD(ROW($A$1:$A$10)-CELL("Row",$A$1:$A$10),2)=0,$A$1:$A$10,))
Set the source range you want to sum.
 
J

jeanulrich00

Ji jean,

To sum every 2nd row in the range $A$1:$A$10, use the following formula:
=SUM(IF(MOD(ROW($A$1:$A$10)-CELL("Row",$A$1:$A$10),2)=0,$A$1:$A$10,))
Set the source range you want to sum.

--
Cheers
macropod
[MVP - Microsoft Word]

I have a file containing many worksheets. As each worksheet have the
same patern I am trying to find a formula that I could use for every
worksheet.
For each worksheet, the column are the same but the number of row can
be different.
Let say I have 10 columns A to J and I have 20 rows starting at row
10. and finishing at row 30
My first need was to be able to sum every even number low for each
column
example D10 + D12 + D14 + D16 + D18 and so on till row 30 Then I need
another formula to sum every odd number row D11 + D13 + D15 + D17 and
so on. As the number of row is not the same I went with DSUM
fonction.
As I understand well the DSUM function I am trying to find a solution
for that :
I need a formula that can replace this (D10*B10) + (D12*B12) +
(D14*B14) + (D16*B16) and so on
I need a formula that will work if I have 20 rows or if I have 100
rows
thanks for helping

The formula is not working, it ias adding all the rows
 
K

keiji kounoike

a formula somthing like =SUMPRODUCT(D10:D30*--NOT(MOD(ROW(D10:D30),2)))
as for D10 + D12 + D14 + D16 + D18 + ... + D30, and
=SUMPRODUCT(D10:D30*MOD(ROW(D10:D30),2)) is for D11 + D13 + D15 + D17 +
... + D30 and
=SUMPRODUCT((D10:D30)*(B10:B30)*--NOT(MOD(ROW(D10:D30),2))) for
(D10*B10) + (D12*B12) + (D14*B14) + (D16*B16) + ... + (D30*B30).

keiji
 
S

Shane Devenshire

Hi,

Try this for both even or odd rows:

=SUMPRODUCT(A1:A25*(MOD(ROW(A1:A25),2)=1)) odd rows
=SUMPRODUCT(A1:A25*(MOD(ROW(A1:A25),2)=0)) even rows

If this helps please click the Yes button

Cheers,
Shane Devenshire


Ji jean,

To sum every 2nd row in the range $A$1:$A$10, use the following formula:
=SUM(IF(MOD(ROW($A$1:$A$10)-CELL("Row",$A$1:$A$10),2)=0,$A$1:$A$10,))
Set the source range you want to sum.

--
Cheers
macropod
[MVP - Microsoft Word]

I have a file containing many worksheets. As each worksheet have the
same patern I am trying to find a formula that I could use for every
worksheet.
For each worksheet, the column are the same but the number of row can
be different.
Let say I have 10 columns A to J and I have 20 rows starting at row
10. and finishing at row 30
My first need was to be able to sum every even number low for each
column
example D10 + D12 + D14 + D16 + D18 and so on till row 30 Then I need
another formula to sum every odd number row D11 + D13 + D15 + D17 and
so on. As the number of row is not the same I went with DSUM
fonction.
As I understand well the DSUM function I am trying to find a solution
for that :
I need a formula that can replace this (D10*B10) + (D12*B12) +
(D14*B14) + (D16*B16) and so on
I need a formula that will work if I have 20 rows or if I have 100
rows
thanks for helping

The formula is not working, it ias adding all the rows
 
M

macropod

Hi jean,

Sorry - it's an array formula, which you need to input with Ctrl-Shift-Enter.

Also, the '=0' should be '=1'.

--
Cheers
macropod
[MVP - Microsoft Word]


Ji jean,

To sum every 2nd row in the range $A$1:$A$10, use the following formula:
=SUM(IF(MOD(ROW($A$1:$A$10)-CELL("Row",$A$1:$A$10),2)=0,$A$1:$A$10,))
Set the source range you want to sum.

--
Cheers
macropod
[MVP - Microsoft Word]

I have a file containing many worksheets. As each worksheet have the
same patern I am trying to find a formula that I could use for every
worksheet.
For each worksheet, the column are the same but the number of row can
be different.
Let say I have 10 columns A to J and I have 20 rows starting at row
10. and finishing at row 30
My first need was to be able to sum every even number low for each
column
example D10 + D12 + D14 + D16 + D18 and so on till row 30 Then I need
another formula to sum every odd number row D11 + D13 + D15 + D17 and
so on. As the number of row is not the same I went with DSUM
fonction.
As I understand well the DSUM function I am trying to find a solution
for that :
I need a formula that can replace this (D10*B10) + (D12*B12) +
(D14*B14) + (D16*B16) and so on
I need a formula that will work if I have 20 rows or if I have 100
rows
thanks for helping

The formula is not working, it ias adding all the rows
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top