Sumif / subtotal / something else?
 

I have had a lot of help here and almost always answer if found with search.
Now I couldn't find such so here it comes.

I have a file whereare columns A: freight numer B: date C: sort D: km E:
amount ... H: eur per frieght

One freight might have 1-7 rows, because each sort (pine, birch etc) makes
own row.

Now I need to find a way to have column I adding total eur per freight. The
problem is that this formula should be already in the cells, so when user is
writing information, sums are appearing without they have to do anything. BUT
the sum should appear only to the last row of the freight. Columns don't have
to be in that order, position can be changed.

Example:

freight date sort km amount/tn ... eur total eur

12 16.1.09 pine 15 27 270 270
13 16.1.09 birch 16 10 150
13 16.1.09 pine 18 12 170 320

This can be made almost how ever, most of cells will be protected. Users
don't know very much about Excel. Thanks in advance any ideas.

Sumif / subtotal / something else?
 

In cell I1 :
=IF(COUNTIF(A1:$A$10,A1)=1,SUMPRODUCT(($A$1:$A$10= A1)*$H$1:$H$10),"")
To be dragged down.
HTH
Daniel
I have had a lot of help here and almost always answer if found with search.
Now I couldn't find such so here it comes.

I have a file whereare columns A: freight numer B: date C: sort D: km E:
amount ... H: eur per frieght

One freight might have 1-7 rows, because each sort (pine, birch etc) makes
own row.

Now I need to find a way to have column I adding total eur per freight. The
problem is that this formula should be already in the cells, so when user is
writing information, sums are appearing without they have to do anything. BUT
the sum should appear only to the last row of the freight. Columns don't have
to be in that order, position can be changed.

Example:

freight date sort km amount/tn ... eur total eur

12 16.1.09 pine 15 27 270 270
13 16.1.09 birch 16 10 150
13 16.1.09 pine 18 12 170 320

This can be made almost how ever, most of cells will be protected. Users
don't know very much about Excel. Thanks in advance any ideas.

Sumif / subtotal / something else?
 

Thanks, I tried but with this I get error #VALUE!


"Daniel.C" kirjoitti:

In cell I1 :
=IF(COUNTIF(A1:$A$10,A1)=1,SUMPRODUCT(($A$1:$A$10= A1)*$H$1:$H$10),"")
To be dragged down.
HTH
Daniel
I have had a lot of help here and almost always answer if found with search.
Now I couldn't find such so here it comes.

I have a file whereare columns A: freight numer B: date C: sort D: km E:
amount ... H: eur per frieght

One freight might have 1-7 rows, because each sort (pine, birch etc) makes
own row.

Now I need to find a way to have column I adding total eur per freight. The
problem is that this formula should be already in the cells, so when user is
writing information, sums are appearing without they have to do anything. BUT
the sum should appear only to the last row of the freight. Columns don't have
to be in that order, position can be changed.

Example:

freight date sort km amount/tn ... eur total eur

12 16.1.09 pine 15 27 270 270
13 16.1.09 birch 16 10 150
13 16.1.09 pine 18 12 170 320

This can be made almost how ever, most of cells will be protected. Users
don't know very much about Excel. Thanks in advance any ideas.

Sumif / subtotal / something else?
 

Hi again,
now I have total sums to freights which has more than 1 row, but only one
row freights are blank. I have tried to change last "" referring to cell
eur/row but this doesn't seem to work when formula is copied.

=IF(COUNTIF(A9:$A$100;A8)=1;SUMPRODUCT(($A$8:$A$10 0=A8)*$O$8:$O$100);"")

This formula is from row 9.

What am I doing wrong? I have a feeling that formula is right but error is
between keyboard and chair :/

"NeedToKnow" kirjoitti:

Thanks, I tried but with this I get error #VALUE!


"Daniel.C" kirjoitti:

In cell I1 :
=IF(COUNTIF(A1:$A$10,A1)=1,SUMPRODUCT(($A$1:$A$10= A1)*$H$1:$H$10),"")
To be dragged down.
HTH
Daniel
I have had a lot of help here and almost always answer if found with search.
Now I couldn't find such so here it comes.

I have a file whereare columns A: freight numer B: date C: sort D: km E:
amount ... H: eur per frieght

One freight might have 1-7 rows, because each sort (pine, birch etc) makes
own row.

Now I need to find a way to have column I adding total eur per freight. The
problem is that this formula should be already in the cells, so when user is
writing information, sums are appearing without they have to do anything. BUT
the sum should appear only to the last row of the freight. Columns don't have
to be in that order, position can be changed.

Example:

freight date sort km amount/tn ... eur total eur

12 16.1.09 pine 15 27 270 270
13 16.1.09 birch 16 10 150
13 16.1.09 pine 18 12 170 320

This can be made almost how ever, most of cells will be protected. Users
don't know very much about Excel. Thanks in advance any ideas.

Sumif / subtotal / something else?
 

The first A9 should be A8. OR, all the 8's should be 9's.

Also, check that the values in O8:O100 are numeric and not text
values.

Hope this helps.

Pete

On Jan 16, 12:30*pm, NeedToKnow
wrote:
Hi again,
now I have total sums to freights which has more than 1 row, but only one
row freights are blank. I have tried to change last "" referring to cell
eur/row but this doesn't seem to work when formula is copied.

=IF(COUNTIF(A9:$A$100;A8)=1;SUMPRODUCT(($A$8:$A$10 0=A8)*$O$8:$O$100);"")

This formula is from row 9.

What am I doing wrong? I have a feeling that formula is right but error is
between keyboard and chair :/

Sumif / subtotal / something else?
 

Big thank you for both of you Daniel.C and Pete_UK!!!
With your help I did it again :D


=IF(COUNTIF(A8:$A$100;A8)=1;SUMPRODUCT(($A$8:$A$10 0=A8)*$O$8:$O$100);"")

"Pete_UK" kirjoitti:

The first A9 should be A8. OR, all the 8's should be 9's.

Also, check that the values in O8:O100 are numeric and not text
values.

Hope this helps.

Pete

On Jan 16, 12:30 pm, NeedToKnow
wrote:
Hi again,
now I have total sums to freights which has more than 1 row, but only one
row freights are blank. I have tried to change last "" referring to cell
eur/row but this doesn't seem to work when formula is copied.

=IF(COUNTIF(A9:$A$100;A8)=1;SUMPRODUCT(($A$8:$A$10 0=A8)*$O$8:$O$100);"")

This formula is from row 9.

What am I doing wrong? I have a feeling that formula is right but error is
between keyboard and chair :/

Sumif / subtotal / something else?
 

Glad to hear it.

Pete

On Jan 16, 4:45*pm, NeedToKnow
wrote:
Big thank you for both of you Daniel.C and Pete_UK!!!
With your help I did it again :D

=IF(COUNTIF(A8:$A$100;A8)=1;SUMPRODUCT(($A$8:$A$10 0=A8)*$O$8:$O$100);"")

"Pete_UK" kirjoitti:



The first A9 should be A8. OR, all the 8's should be 9's.

Also, check that the values in O8:O100 are numeric and not text
values.

Hope this helps.

Pete

On Jan 16, 12:30 pm, NeedToKnow
wrote:
Hi again,
now I have total sums to freights which has more than 1 row, but only one
row freights are blank. I have tried to change last "" referring to cell
eur/row but this doesn't seem to work when formula is copied.

=IF(COUNTIF(A9:$A$100;A8)=1;SUMPRODUCT(($A$8:$A$10 0=A8)*$O$8:$O$100);"")

This formula is from row 9.

What am I doing wrong? I have a feeling that formula is right but error is
between keyboard and chair :/- Hide quoted text -

- Show quoted text -