# Exceptions formula

#### propilot81

##### New Member
I have a data set where I am trying to create a formula for each work order that does not have a specific task ("Task 5" Below Table) within the work order based on specific customers that must have this task with their work. There will be customers that do not apply and will not need this task performed. I am trying to create the formula in column D.
As seen in the example below, customer A1111 must have Task 5 in their work order. In work order 111111, Task 5 is present and work order is OK, however, in work order 333333 Task 5 is not present for customer A1111 and needs to be flagged "Need Task 5." Customer B1111 is a customer that does not apply.

Column AColumn BColumn CColumn D

<tbody>
</tbody>
111111A1111Ok

<tbody>
</tbody>
111111A1111Ok

<tbody>
</tbody>

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
do you mean
however, in work order 333333 Task 5 is not present for customer A1111
should that read Customer C1111 ?
How would excel know that B1111 does not apply

you could use a countIFS()

in column D

and copy down
That can be combined with an IF()

BUT , i dont know how to identify Not Applicable

Wayne,

My apologies, customer should read A1111 for work order 333333. This customer needs "Task 5" on any work order created.

so not C1111
It will still work

but how does excel know "Not Applicable"

so not C1111
It will still work

but how does excel know "Not Applicable"

That's one challenge I am trying to figure out. The majority of the customer's will be "Not Applicable," so maybe an if statement would help. There are only a handful of customers that need to have "Task 5" with each new work order generated under their customer number.

That's one challenge I am trying to figure out. The majority of the customer's will be "Not Applicable," so maybe an if statement would help. There are only a handful of customers that need to have "Task 5" with each new work order generated under their customer number.

Wayne,

I made a couple of changes to the formula and set up the customers with a "Task 5" requirement in a separate table in column "I". This formula is working. Thanks for all the help!

Replies
1
Views
210
Replies
6
Views
187
Replies
1
Views
402
Replies
4
Views
145
Replies
1
Views
90

1,203,534
Messages
6,055,956
Members
444,839
Latest member
laurajames

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back