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


--
JackSchilder
------------------------------------------------------------------------
JackSchilder's Profile: http://www.excelforum.com/member.php...o&userid=31577
View this thread: http://www.excelforum.com/showthread...hreadid=512705

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" wrote:


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


--
JackSchilder
------------------------------------------------------------------------
JackSchilder's Profile: http://www.excelforum.com/member.php...o&userid=31577
View this thread: http://www.excelforum.com/showthread...hreadid=512705

Thanks for the quick reply.

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

Gizmo63 Wrote:
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

"[/color]


--
JackSchilder
------------------------------------------------------------------------
JackSchilder's Profile: http://www.excelforum.com/member.php...o&userid=31577
View this thread: http://www.excelforum.com/showthread...hreadid=512705

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


"JackSchilder" wrote:


Thanks for the quick reply.

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

Gizmo63 Wrote:
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
------------------------------------------------------------------------
JackSchilder's Profile: http://www.excelforum.com/member.php...o&userid=31577
View this thread: http://www.excelforum.com/showthread...hreadid=512705

[/color]

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 ;)


--
JackSchilder
------------------------------------------------------------------------
JackSchilder's Profile: http://www.excelforum.com/member.php...o&userid=31577
View this thread: http://www.excelforum.com/showthread...hreadid=512705

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

"JackSchilder" wrote:


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 ;)


--
JackSchilder
------------------------------------------------------------------------
JackSchilder's Profile: http://www.excelforum.com/member.php...o&userid=31577
View this thread: http://www.excelforum.com/showthread...hreadid=512705