Calculating total dollars and percentages by job

spreadsheet.jpg

I am setting up spreadsheet for our Quality Department which needs to know total dollar and percentage of non-conformance's by department and by job #. I have by department set up and done however I'm having issues figuring out what formula to use for job #. The smaller chart on the left is what I need filled in. I need to select all of one job from column A and add up their totals in column g to fill column n (total $) and repeat for column 0 (total %). Note that one job may have 3 issues while another job may have 6 and the next may only have 1 issue.

You really need to change the title of your post to something more descriptive of your problem.

http://www.excelforum.com/forum-rule...rum-rules.html

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more days have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

you're referring to the title I assume?
Pbly something related to the issue, not necessarily the formula. So you need help calculating total dollars for specific jobs? Is that right?

As an FYI, it would be easier to write formulas for you if you actually posted a sample spreadsheet with real but not confidential data contained. This isn't a rule and I don't want to be a wet blanket but nobody wants to try to rewrite your data to solve your issue.

From what i can see it looks like you're looking for a =sumif(A:A,m6,G:G) in cell N6, and drag down.
still thinking about the second one.

To understand what you want in col O, give me an idea of what values should be there for say 17749?
And have you tried the forumla i gave you in post # 7?

so in col O this would work, though it is a little inelligant...
=sumif(A:A,m6,G:G)/sumif(A:A,m6,H:H) and format as percent, should work.

Both? Great! And I wasn't trying to pick apart your post, just trying to be helpful to a newcomer, my recs were either rules or they get you help faster.

Hold the horses there filly_05 (no pun intended), I don't think that second formula is right. I summed the value in col H for 17749 but I suspect that is a "total" value not to be summed. Is that right, in other words the value of $100,000 is the total value for 17749, not $100,000 for each line item? If the latter is true then I have to rewrite the formula for you, if the former then it is ok to go. Let me know which.

EDIT: since i haven't heard from you (i'm sure you have other stuff to do) I'm going to assume the total value for job# 17749 is actually $100,000 and not $200,000 as my post #11 assumes. If so this formula should work in place of the other, put in col O (cell O6) =sumif(A:A,m6,G:G)/vlookup(M6,A:H,8,FALSE)

EDIT2: or you can repoint the other formula to this...
=sumif(A:A,m6,G:G)/sumif(A:A,m6,J:J)