Sum based on criteria (if, then?)

[tims]

I have the following that I need calculations applied to for $ sum:

A B C D E F G H
S123 s123 1.50 12 2.25 0 $22.50 $22.50
S123 s123 1.05 12 1.75 0 $17.50 $40.00
S123 s123 0.50 11 0.50 1 $5.00 $45.00
S123 s123 1.25 0 0 0 $0.00 $0.00
S123 s123-1 0.75 13 0.75 0 $7.50 $7.50
S123 s123-1 2.25 13 3.25 0 $30.25 $37.75
S123 s123-1 1.75 11 1.75 2 $10.75 $48.50
S123 s123-1 1.50 10 2.25 1 $20.25 $68.75
S123 s123-2 1.75 10 1.75 0 $10.75 $10.75

I need a running tally based scrap (F or last column) and run rate dollars for the above. The running tally will be based on actual run rate (E or 2nd last column) times applied run rate (ie: $10/hr). The tally will be based on matching numbers in column B. Column G is example of data summary that I need auto calc formula. Any help is greatly appreciated.

[Bryan Hessey]

I have the following that I need calculations applied to for $ sum:

A B C D E F G H
S123 s123 1.50 12 2.25 0 $22.50 $22.50
S123 s123 1.05 12 1.75 0 $17.50 $40.00
S123 s123 0.50 11 0.50 1 $5.00 $45.00
S123 s123 1.25 0 0 0 $0.00 $0.00
S123 s123-1 0.75 13 0.75 0 $7.50 $7.50
S123 s123-1 2.25 13 3.25 0 $30.25 $37.75
S123 s123-1 1.75 11 1.75 2 $10.75 $48.50
S123 s123-1 1.50 10 2.25 1 $20.25 $68.75
S123 s123-2 1.75 10 1.75 0 $10.75 $10.75

I need a running tally based scrap (F or last column) and run rate dollars for the above. The running tally will be based on actual run rate (E or 2nd last column) times applied run rate (ie: $10/hr). The tally will be based on matching numbers in column B. Column G is example of data summary that I need auto calc formula. Any help is greatly appreciated.

Hi,

I believe that rows 6, 7, 8 & 9 of your sample data do not fit your '$10/hr' thought.

in H1 put =G1
in H2 put =IF(F1=1,G2,G2+H1)

you could use =IF(F1=1,E2*10,(E2*10)+H1)

After you fix your G column amounts then your data should total correctly, the $10 rate should be in a cell rather than coded into a formula.

hth
---

[duane]

I am not following you. Initially it looks like column G = column E * 10, but then on line 6 that changes to something that is unclear.

[Bryan Hessey]

I am not following you. Initially it looks like column G = column E * 10, but then on line 6 that changes to something that is unclear.

Hi duane, I think it's just a mistype or a mis-addition, ie 30.25 for 32.50 etc

I do need this last reply with a couple of alterations.

1) I will use a separate cell to apply the hourly rate as suggested.

2) =IF(F1>1,E2*10,(E2*10)+H1) - already changed due to the value being 1 or greater to originate calc

this change disagrees with your next request re column B -

Your first post showed that only 1 in F caused a break, the 2 did not.
"I need a running tally based scrap (F or last column"

2) I need the calc to recognize the different value in column B to continue or end the sum in column H

tims

then use (on row 2) ... (row 4 of your original display was in error for this request)

=IF(B1<>B2,E2*10,(E2*10)+H1)

hth
---

[tims]

Rate $1.00
scrap cost total $0.57

u job l job Qty run Act hrs scrap qty cost/prt ttl cost/part scrap cost
S123 s123 12 2.25 1 $0.19 $0.19 $0.19
S123 s123 12 1.75 0 $0.15 $0.33 $0.00
S123 s123 11 0.5 1 $0.05 $0.38 $0.38
S123 s123 0 0 0 #DIV/0! #DIV/0! #DIV/0!
S123 s123-1 13 0.75 0 $0.06 FALSE $0.00
S123 s123-1 13 3.25 0 $0.25 $0.25 $0.00
S123 s123-1 11 1.75 2 $0.16 $0.41 $0.82
S123 s123-1 10 2.25 1 $0.23 $0.63 $0.63
S123 s123-2 10 1.75 1 $0.18 FALSE $0.00

I provided updated table to simplify (I hope). It is very close, but not sure how to solve for "false" statements. This worksheet currently using =IF(B9=B8,F9+G8). I need to correct to show value ($0.06) for row 8 and value ($0.18) for start or initial value of "l job"

[Bryan Hessey]

Rate $1.00
scrap cost total $0.57

u job l job Qty run Act hrs scrap qty cost/prt ttl cost/part scrap cost
S123 s123 12 2.25 1 $0.19 $0.19 $0.19
S123 s123 12 1.75 0 $0.15 $0.33 $0.00
S123 s123 11 0.5 1 $0.05 $0.38 $0.38
S123 s123 0 0 0 #DIV/0! #DIV/0! #DIV/0!
S123 s123-1 13 0.75 0 $0.06 FALSE $0.00
S123 s123-1 13 3.25 0 $0.25 $0.25 $0.00
S123 s123-1 11 1.75 2 $0.16 $0.41 $0.82
S123 s123-1 10 2.25 1 $0.23 $0.63 $0.63
S123 s123-2 10 1.75 1 $0.18 FALSE $0.00

I provided updated table to simplify (I hope). It is very close, but not sure how to solve for "false" statements. This worksheet currently using =IF(B9=B8,F9+G8). I need to correct to show value ($0.06) for row 8 and value ($0.18) for start or initial value of "l job"

the solution was shown in my last post, also, what are you doing at F4 to produce a #Div/0 error ?

---