Help with a complex formula
 

i am working in excell 2003 and have little hair left.

Any help will be appreciated. I will even be glad to mail this spreadsheet
to someone so that they can see exactly what i am doing.

here goes

If the number 1 is is in a cell within the column range of D3 to D32, then
add the number in the corresponding row cell Hx (where X equals the row
number), to the total at cell H34 and cntinue to the next row.

example

If the cell D3=1, then add the number in the cell H3 to thee the running
total in H34 and record the new Number in H34.

next row

If thte cell D4=1,Then add the number in the cell H4 to the running total
that was written to H34, and record the new number in H34

continue to the end. H32


I have to be able to insert more rows between row 3 and 32 so that more
information can be replicated there. adding rows would change the running
total cell respectivly.

The reason for this spreadsheet is to keep track of the number of newspapers
given to clients and picked up from clients on a daily basis. hence wanting
to add more rows as clients are added with a calculation to split them to
racks, stores,delivery venders, ect while keeping running totals as the
spreadsheet sees various types of deliverys.

if a 1 is not in the trigger cell ( example D3) then nothing happens to
that formula

Thanks in advance for any help.

Frank Freeman
707-557-4066

Help with a complex formula
 

Frank,

one way to do this, if I have understood your problem right, is to test
the value in column D, and enter a 0 or 1 in another column based on
the result. That means you enter

=if(Dx=1;1;0) in the same line of another column

in a free column.
Then you enter an array formula in cell D34.

I have made a worksheet to test this, and have placed the if's in
column I.

The formula in cell D34 is

=sum(H3:H32*I3:I32)
Since this is an array formula you must hold down ctrt + shift when you
press enter. EXCEL will insert {} outside your formula. (That can not
be done manually, ctrl + shift tells EXCEL to do it).

I have tested inserting lines, and it works. Once you enter values in
columns D and H, the formula in I will be copied automatically.

Hope this solves your problem. I can e-mail you my file if you want it.



frank.freeman wrote:
i am working in excell 2003 and have little hair left.

Any help will be appreciated. I will even be glad to mail this spreadsheet
to someone so that they can see exactly what i am doing.

here goes

If the number 1 is is in a cell within the column range of D3 to D32, then
add the number in the corresponding row cell Hx (where X equals the row
number), to the total at cell H34 and cntinue to the next row.

example

If the cell D3=1, then add the number in the cell H3 to thee the running
total in H34 and record the new Number in H34.

next row

If thte cell D4=1,Then add the number in the cell H4 to the running total
that was written to H34, and record the new number in H34

continue to the end. H32


I have to be able to insert more rows between row 3 and 32 so that more
information can be replicated there. adding rows would change the running
total cell respectivly.

The reason for this spreadsheet is to keep track of the number of newspapers
given to clients and picked up from clients on a daily basis. hence wanting
to add more rows as clients are added with a calculation to split them to
racks, stores,delivery venders, ect while keeping running totals as the
spreadsheet sees various types of deliverys.

if a 1 is not in the trigger cell ( example D3) then nothing happens to
that formula

Thanks in advance for any help.

Frank Freeman
707-557-4066

Help with a complex formula
 

Frank,

one way to do this, if I have understood your problem right, is to test
the value in column D, and enter a 0 or 1 in another column based on
the result. That means you enter

=if(Dx=1;1;0) in the same line of another column

in a free column.
Then you enter an array formula in cell D34.

I have made a worksheet to test this, and have placed the if's in
column I.

The formula in cell D34 is

=sum(H3:H32*I3:I32)
Since this is an array formula you must hold down ctrt + shift when you
press enter. EXCEL will insert {} outside your formula. (That can not
be done manually, ctrl + shift tells EXCEL to do it).

I have tested inserting lines, and it works. Once you enter values in
columns D and H, the formula in I will be copied automatically.

Hope this solves your problem. I can e-mail you my file if you want it.



frank.freeman wrote:
i am working in excell 2003 and have little hair left.

Any help will be appreciated. I will even be glad to mail this spreadsheet
to someone so that they can see exactly what i am doing.

here goes

If the number 1 is is in a cell within the column range of D3 to D32, then
add the number in the corresponding row cell Hx (where X equals the row
number), to the total at cell H34 and cntinue to the next row.

example

If the cell D3=1, then add the number in the cell H3 to thee the running
total in H34 and record the new Number in H34.

next row

If thte cell D4=1,Then add the number in the cell H4 to the running total
that was written to H34, and record the new number in H34

continue to the end. H32


I have to be able to insert more rows between row 3 and 32 so that more
information can be replicated there. adding rows would change the running
total cell respectivly.

The reason for this spreadsheet is to keep track of the number of newspapers
given to clients and picked up from clients on a daily basis. hence wanting
to add more rows as clients are added with a calculation to split them to
racks, stores,delivery venders, ect while keeping running totals as the
spreadsheet sees various types of deliverys.

if a 1 is not in the trigger cell ( example D3) then nothing happens to
that formula

Thanks in advance for any help.

Frank Freeman
707-557-4066

Help with a complex formula
 

Hi again, Frank,

I have looked at your problem again, and found a couple of other ways
to solve it.

1: use sumif function;

in H34, enter

=SUMIF(D3:D32;"=1";H3:H32)

2: use conditional sum vizard (requires an add-in)

the formula in H34 would look like

=SUM(IF($D$3:$D$32=1;$H$3:$H$32;0))

You can also enter that formula as an array formula, ref. my first reply

Help with a complex formula
 

Hi again, Frank,

I have looked at your problem again, and found a couple of other ways
to solve it.

1: use sumif function;

in H34, enter

=SUMIF(D3:D32;"=1";H3:H32)

2: use conditional sum vizard (requires an add-in)

the formula in H34 would look like

=SUM(IF($D$3:$D$32=1;$H$3:$H$32;0))

You can also enter that formula as an array formula, ref. my first reply

Help with a complex formula
 

=sumif(D3:D32,1,H3:H32)

--
Regards,
Tom Ogilvy

"frank.freeman" wrote in message
...
i am working in excell 2003 and have little hair left.

Any help will be appreciated. I will even be glad to mail this spreadsheet
to someone so that they can see exactly what i am doing.

here goes

If the number 1 is is in a cell within the column range of D3 to D32, then
add the number in the corresponding row cell Hx (where X equals the row
number), to the total at cell H34 and cntinue to the next row.

example

If the cell D3=1, then add the number in the cell H3 to thee the running
total in H34 and record the new Number in H34.

next row

If thte cell D4=1,Then add the number in the cell H4 to the running total
that was written to H34, and record the new number in H34

continue to the end. H32


I have to be able to insert more rows between row 3 and 32 so that more
information can be replicated there. adding rows would change the running
total cell respectivly.

The reason for this spreadsheet is to keep track of the number of
newspapers
given to clients and picked up from clients on a daily basis. hence
wanting
to add more rows as clients are added with a calculation to split them to
racks, stores,delivery venders, ect while keeping running totals as the
spreadsheet sees various types of deliverys.

if a 1 is not in the trigger cell ( example D3) then nothing happens to
that formula

Thanks in advance for any help.

Frank Freeman
707-557-4066

Help with a complex formula
 

thanks for all the help from all you guys

thix put me on the right track

"Tom Ogilvy" wrote:

=sumif(D3:D32,1,H3:H32)

--
Regards,
Tom Ogilvy

"frank.freeman" wrote in message
...
i am working in excell 2003 and have little hair left.

Any help will be appreciated. I will even be glad to mail this spreadsheet
to someone so that they can see exactly what i am doing.

here goes

If the number 1 is is in a cell within the column range of D3 to D32, then
add the number in the corresponding row cell Hx (where X equals the row
number), to the total at cell H34 and cntinue to the next row.

example

If the cell D3=1, then add the number in the cell H3 to thee the running
total in H34 and record the new Number in H34.

next row

If thte cell D4=1,Then add the number in the cell H4 to the running total
that was written to H34, and record the new number in H34

continue to the end. H32


I have to be able to insert more rows between row 3 and 32 so that more
information can be replicated there. adding rows would change the running
total cell respectivly.

The reason for this spreadsheet is to keep track of the number of
newspapers
given to clients and picked up from clients on a daily basis. hence
wanting
to add more rows as clients are added with a calculation to split them to
racks, stores,delivery venders, ect while keeping running totals as the
spreadsheet sees various types of deliverys.

if a 1 is not in the trigger cell ( example D3) then nothing happens to
that formula

Thanks in advance for any help.

Frank Freeman
707-557-4066

Help with a complex formula
 

thanks i would like to see you sheet..



thanks

"Norman" wrote:

Frank,

one way to do this, if I have understood your problem right, is to test
the value in column D, and enter a 0 or 1 in another column based on
the result. That means you enter

=if(Dx=1;1;0) in the same line of another column

in a free column.
Then you enter an array formula in cell D34.

I have made a worksheet to test this, and have placed the if's in
column I.

The formula in cell D34 is

=sum(H3:H32*I3:I32)
Since this is an array formula you must hold down ctrt + shift when you
press enter. EXCEL will insert {} outside your formula. (That can not
be done manually, ctrl + shift tells EXCEL to do it).

I have tested inserting lines, and it works. Once you enter values in
columns D and H, the formula in I will be copied automatically.

Hope this solves your problem. I can e-mail you my file if you want it.



frank.freeman wrote:
i am working in excell 2003 and have little hair left.

Any help will be appreciated. I will even be glad to mail this spreadsheet
to someone so that they can see exactly what i am doing.

here goes

If the number 1 is is in a cell within the column range of D3 to D32, then
add the number in the corresponding row cell Hx (where X equals the row
number), to the total at cell H34 and cntinue to the next row.

example

If the cell D3=1, then add the number in the cell H3 to thee the running
total in H34 and record the new Number in H34.

next row

If thte cell D4=1,Then add the number in the cell H4 to the running total
that was written to H34, and record the new number in H34

continue to the end. H32


I have to be able to insert more rows between row 3 and 32 so that more
information can be replicated there. adding rows would change the running
total cell respectivly.

The reason for this spreadsheet is to keep track of the number of newspapers
given to clients and picked up from clients on a daily basis. hence wanting
to add more rows as clients are added with a calculation to split them to
racks, stores,delivery venders, ect while keeping running totals as the
spreadsheet sees various types of deliverys.

if a 1 is not in the trigger cell ( example D3) then nothing happens to
that formula

Thanks in advance for any help.

Frank Freeman
707-557-4066