Plz help.. When a prodhiernr changes i want to sum the costs for this product.

[JackSchilder]

I also posted this on excel programming im desperate for a reply.

Hi there,

so i have for example :

prodhiernr artnr cost
1110050 123456 50
1110050 123544 60
1110050 234343 70
1110051 534222 90
1110051 353533 100
1110051 124643 80

When de prodhiernr changes to 1110051 i want to sum the total cost for
prodhiernr 1110050 and later on for the other products also (the list
is very long).. Does anyone have a clue how to do this?

Thanks in advance

 

[Guest]

If this is just a data dump then the easiest way is to use the sub total
function.

Highlight the data including the headers, select Data > Subtotals

At each change in : prodhiernr
Use function: Sum
Add subtotal to: [check the cost box]

Click OK

If you want a formula to run in a column next to your data then post up and
we'll try to resolve it.

hth
Giz

 

[JackSchilder]

Thanks for the quick reply.

I actually was looking for a formula to run in a column next to it.

If this is just a data dump then the easiest way is to use the sub
total
function.

Highlight the data including the headers, select Data > Subtotals

At each change in : prodhiernr
Use function: Sum
Add subtotal to: [check the cost box]

Click OK

If you want a formula to run in a column next to your data then post up
and
we'll try to resolve it.

hth
Giz

">

[/QUOTE]

 

[Guest]

Here you go Jack,

Assume your data table in cols A-B-C, new subtotals in col D and headers in
row 1.
Put the following in D" and drag down:

=IF(B2=B3,"",SUMPRODUCT(--($B$1:$B$10000=B2),$D$1:$D$10000))

Unfortunately in a sumproduct you cannot simply specify a column you have to
define a set of cells, so just need to make sure it's big enough.

hth

Giz

Thanks for the quick reply.

I actually was looking for a formula to run in a column next to it.

If this is just a data dump then the easiest way is to use the sub
total
function.

Highlight the data including the headers, select Data > Subtotals

At each change in : prodhiernr
Use function: Sum
Add subtotal to: [check the cost box]

Click OK

If you want a formula to run in a column next to your data then post up
and
we'll try to resolve it.

hth
Giz

">

[/QUOTE]

 

[JackSchilder]

Thanks a lot for your reply.

Ill give it a try

To bad excel doesnt auto calculate the amount of rows!

Im using Jetreports to get the data out of a database, nice program but
a bit hard to handle at start when you dont much of excel

 

[Guest]

Here's a thought, if the data is already sorted then use
=IF(B2=B3,"",SUMPRODUCT(--($B$2:$B2=B2),$D$2:$D2))

The headers are in row 1, the starting point is in row 2 and the range end
(initially $b2) will grow as you drag it down the rows. Now it only looks at
the rows above the formula instead of the whole column.

Should have thought of it in the first place :-(

Giz