Adding all even number row

[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

 

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

 

[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]

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

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

 

[keiji kounoike]

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

keiji

 

[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]

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

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

 

[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]

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

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