Sum Specific Line in Job Based on another Condition

[CrocHntr]

Hi,

I've got some code data that I'm trying to summarize. Basically I have a list of Jobs (Work Orders). I want to add all the $'s assigned to the JC679 code in my list of jobs, but ONLY IF the job (work order) also contains a JC304 code.

This will help me greatly to break out costs on my invoicing. Thanks so much!

WO_Example.jpg

[DBY]

Hello
Perhaps something like:

Please Login or Register  to view this content.

This is for the cells with the 12345 Work Order and copy down for the others.

DBY

[CrocHntr]

Hello
Perhaps something like:

Please Login or Register  to view this content.

This is for the cells with the 12345 Work Order and copy down for the others.

DBY

No, unfortunately that didn't work. To use that formula, I would have to change the range for each work order. It doesn't build a criteria that excludes the calculation if the work order doesn't have a JC304 specifically on it. I need one formula that I can bring down over thousands of work orders / lines. Thanks for the suggestion though.

Anyone else?

[DBY]

Hi
Sorry it didn't work. Are the Work Orders always in groups of three as in your example?

*Just noticed the last one is two so I've answered my own question. I'll see if I can amend the formula.

[CrocHntr]

Yeah, there can be any combination of lines for a given work order. It's a tough one

[DBY]

Hello
If your actual data layout is the same as your example and is consistent, perhaps you could create a unique list of the Work Orders using Advanced Filter and build a totals table for each Order. You could then use Vlookup to reference this table if you wished to drag down the formulas alongside your data.

See the attached example. Hope this is of some help.
DBY

[CrocHntr]

Thanks. I can certainly give that a try. A few extra steps (I was hoping for a one step formula), but it does seem to work. Thanks again for all the help with this one!

[DBY]

Glad it's useful, if I can come up with a one step formula I'll get back, but I'm finding it tricky.

[DBY]

If you're still looking for a single formula to total each Work Order as above, then try the following in cell D2 down in the example file I posted:

Please Login or Register  to view this content.

Be aware of the various absolute Row references for this to work.
DBY

[CrocHntr]

If you're still looking for a single formula to total each Work Order as above, then try the following in cell D2 down in the example file I posted:

Please Login or Register  to view this content.

Be aware of the various absolute Row references for this to work.
DBY

Oh WOW! That did the trick! I appreciate you sticking with this one. Well done. I never would've figured it out.

Thanks again,

Rob

[newdoverman]

Here is a solution that includes a helper column to make the calculation simple. The helper column will have YES if JC304 is included in the Work Order.

The formula to sum JC679 will only do the sum if the Work Order contains a YES in the helper column.

[CrocHntr]

Yep, that one works too. Having the helper column just makes it easier for my tired mind to understand, and the overall concept is the same.

Thanks so much!

[newdoverman]

Sometimes helper columns make things much easier to understand and execute.

Thank you for the feedback.