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

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

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.

Originally Posted by Gizmo63

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]

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

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