Sumif / subtotal / something else?

[NeedToKnow]

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.

 

[Daniel.C]

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

 

[NeedToKnow]

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


"Daniel.C" kirjoitti:

 

[NeedToKnow]

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$100=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:

 

[Pete_UK]

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

 

[NeedToKnow]

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


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

"Pete_UK" kirjoitti:

 

[Pete_UK]

Glad to hear it.

Pete

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

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

"Pete_UK" kirjoitti:






- Show quoted text -