Excel Formulae 2

[kiw1]

2536710 5/7/04 6.00
2536710 5/7/04 6.00 12.00
2537730 17/1/05 6.00 6.00
2537735 5/7/04 2.00
2537735 19/9/04 2.00 4.00

Here's the story. We have a large spreadsheet that does job costings. The 1st column is the job number, 2nd obviously is date, the 3rd is the rate for the job and the 4th column is the total for the job.

What we want to have is a formula that will check column 1 and see if the value in it is the same as the next row. If it is it will keep checking until it finds a different number. When it doens't find a match it will total all the 3rd column for that job number and put the result in row 4. It will then carry on doing the same until the end of the spreadsheet.

Is this possible?

Thanks

 

[dhulbert]

Kik1

Try this

=IF(A1=A2,"",SUMIF(A1:A8,A1,C1:C8))

Colunm A is where your job no's and column C is your cost.

Just copy it down and extend the ranges as required.

ASCII stupid question, get a stupid ANSI!

 

[kiw1]

Thanks Dhulbert,

That does most of what we want, and certainly is a large step in the right direction. Is it possible to make it so that it will only give 1 value (ie the total) for each job number. If I extend the formula down the whole sheet, the first value it gives is the grand total for that job number, the next line then gives the a total minus the first line (ie it's checking the same criteria and doing the same thing just with 1 less row of data).

Is bossible to do add another if statement along the lines of if jobnumber = jobnumber in row above stop, make a blank result? I guess it would need to do that after running the first formula?

Thanks for your help

 

[kiw1]

Thanks Dhulbert,

That does most of what we want, and certainly is a large step in the right direction. Is it possible to make it so that it will only give 1 value (ie the total) for each job number. If I extend the formula down the whole sheet, the first value it gives is the grand total for that job number Exactly as we want), the next line then gives the a total minus the first line (ie it's checking the same criteria and doing the same thing just with 1 less row of data). It would be nice if the result column only had a 1 value for each of the jobnumbers - their total

Is it possible to add another if statement along the lines of if jobnumber = jobnumber in row above stop, make a blank result? I guess it would need to do that after running the first formula?

Thanks for your help

 

[reichertc]

Didn't quite understand where you were going with your reply, but I think Dhulbert intended some fixed references like:

=IF(A4=A5,"",SUMIF($A$4:$C$8,A4,$C$4:$C$8))

That will output the fourth column the way that you have shown it above.

 

[coachdan]

Have you experimented with a pivot table? You may be able to get your desired result that way if I understand what you are looking for.

coachdan32

 

[KenWright]

This is exactly what Data / Subtotals was designed to do.

Make sur eyour data is sorted and ordered on Column1 and then select it all and do data / subtotals / at each change in Column 1 total column 3 etc

Would also consider a pivot table though.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[kiw1]

Thanks Ken,

That's so simple and obvious, once you know! I'll look into pivot tables too. Thanks everyone for your help.