Formula to total all subtotals for only 1 job

[Yozhik]

I'm a courier. I have an excel invoice. Each job is listed on this invoice from the top down with columns named DATE, CUSTOMER, JOB #, PICK UP / DEL LOCATION, DISTANCE, SUBTOTAL, JOB TOTAL.

If a job has no special expenses or charges tied to it, then it only takes up one row. If there are special charges, I use the next 1 to 3 rows to list those. I start by highlighting the first 3 columns of the next 1 to 3 rows (DATE, CUSTOMER, JOB #), selecting Merge & Center, entering "ADDITIONAL CHARGES >>>" in the newly merged block of cells and beginning below the "PICK UP / DEL LOCATION" column(to the right of newly merged block, I list the additional charges, however many there may be. They could be any of the following. Weight, Wait Time, After Hours, Weekend, Holiday, and Extra Stop.

I already have a formula that searches for these words, extracts the number behind such a word and calculates a subtotal based on these values. (shown below) I now need a formula that can add the subtotal of each job and populate the JOB TOTAL column, no matter how many rows of additional charges there are below a particular job, and without mistakenly counting subtotals of the next job. Any help is greatly appreciated.

Formula for Subtotals:
{=IF(ISBLANK(D2),"",IF(ISNUMBER(SEARCH("WEIGHT",D2)),(0.05)*(1*MID(D2,MATCH(TRUE,ISNUMBER(1*MID(D2,ROW($1:$20),1)),0),COUNT(1*MID(D2,ROW($1:$20),1)))-100),IF(ISNUMBER(SEARCH("WAIT",D2)),(0.2)*(1*MID(D2,MATCH(TRUE,ISNUMBER(1*MID(D2,ROW($1:$20),1)),0),COUNT(1*MID(D2,ROW($1:$20),1)))-15),IF(D2="WEEKEND",6,(IF(D2="AFTER HOURS",3,(IF(D2="HOLIDAY",12,IF(D2="EXTRA STOP",6,IF(E2="LOCAL",15,(E2*0.9)))))))))))}

 

[GlennUK]

Sorry but I'm finding it hard to imagine your layout, and what parts of it you want to total. Can you provide a snapshot?

 

[Yozhik]

http://gadshack.com/test/invoice1.xlsx

I'll give you what I've got so far.

In the JOB TOTAL column I need it to show the JOB TOTAL (total of all subtotals for a specific job) on the last row of each job. I already have the last column checking the first cell of the NEXT row for the word "ADDITIONAL CHARGES" and if it finds it, then it stays blank, like it should. I just don't know how to do the rest of it.

 

[GlennUK]

This array formula entered into cell G2 and copied down might work:

=IF(OR(A2="",ISNUMBER(SEARCH("ADDITIONAL CHARGES",A2))),"",SUM(F2:INDEX($F$1:$F$999,MAX(MIN(IF(A3:A5<>"",IF(A3:A5<>"ADDITIONAL CHARGES >>>",ROW(A3:A5),999)))-1,ROW()))))

... but do some testing for all the different cases you can think of.

 

[Peter_SSs]

Assuming column F has an entry on all lines, would this work for you in G2 (copied down)?

=IF(AND(C3="",N(F3)),"",SUM(F$1:F2)-SUM(G$1:G1))

 

[Yozhik]

@Glenn: Thank you very much for your effort. I tried your formula and with every variable I could think of and it seemed to miscalculate the totals. The totals were too high. Also the JOB TOTAL displayed on the first row of each job, not the last. Again, I very much appreciate your help here anyway.

@Peter: You are a gentleman and a scholar...Truly. Who would've thought that it could be done so easily...besides you of course. Many thanks!

P.S. I did use Peter's formula, but added a

=IF(ISBLANK(D2),"",(

to the beginning of it so that it wouldn't show zeros on empty lines. (Column D will always be populated in any row that is used)

 

[Peter_SSs]

Cheers, thanks for the feedback.

BTW, unless you like typing, in most circumstances this more compact version should work just as well
=IF(D2="","", ..

Are you able to explain in words what the 'Formula for Subtotals' is doing? There might possibly be a simpler formula there too.