Help with tricky IF formula

[CptTroll]

Hi Excel Forum!

First time poster here, but I have lurked for the past few months and am grateful for all the great tips that have been shared.

Original request: I am unsure how to create a formula that checks if a business has a trained employee. Currently, my formulas return the business as untrained if they have one person who isn't trained - I'm trying to ignore this if they have another employee who IS trained.

Edited request: Column C (Trained?) is the information that I am trying to produce. I will give a scenario example:

A construction business has two employees. One is trained but one isn't. I would like the business to be marked as trained, ignoring the employee who isn't trained (because one employee is trained).

The goal is to create a pivot table that marks specific businesses as being 'trained' or 'untrained' so I can target businesses who need training. Currently they show up as 'untrained' if they have at least one employee marked as 'untrained', despite having another employee who is 'trained'. For this reason I am creating this additional column but it's tricky to get the formula to grab from multiple instances of rows.

An example table:

Business // Employee Trained // Business Trained? (Insert Formula Here)
Construction // Yes // Yes
Construction // No // Yes
Hairdresser // Yes // Yes
Government // No // No
Warehouse // Yes // Yes
Warehouse // Yes // Yes


Thanks in advance for all advice and assistance.

[FlameRetired]

CptTroll welcome to the forum.

With the data in columns A2:C7 enter this in D2 and fill down.

Please Login or Register  to view this content.

	A	B	C	D
1	Business	EmployeeTrained		Trained
2	Construction	Yes	Yes	Trained
3	Construction	No	Yes	Trained
4	Hairdresser	Yes	Yes	Trained
5	Government	No	No	Not trained
6	Warehouse	Yes	Yes	Trained
7	Warehouse	Yes	Yes	Trained

Does this help?

[FlameRetired]

If that doesn't quite do what you want try this formula.

Please Login or Register  to view this content.

	A	B	C	D
1	Business	EmployeeTrained		Trained?
2	Construction	Yes	Yes	Trained
3	Construction	No	Yes	Trained
4	Hairdresser	Yes	Yes	Trained
5	Government	No	No	Not trained
6	Warehouse	Yes	Yes	Trained
7	Warehouse	Yes	Yes	Trained

[CptTroll]

Thanks for the quick reply, guys!

Your suggestions are great but unfortunately I worded my request wrong - I will re-word the initial post as well.

Column C (Trained?) is the information that I am trying to produce. I will give a scenario example:

A construction business has two employees. One is trained but one isn't. I would like the business to be marked as trained, ignoring the employee who isn't trained (because one employee is trained).

The goal is to create a pivot table that marks specific businesses as being 'trained' or 'untrained' so I can target businesses who need training. Currently they show up as 'untrained' if they have at least one employee marked as 'untrained', despite having another employee who is 'trained'. For this reason I am creating this additional column but it's tricky to get the formula to grab from multiple instances of rows.

Let me know if that makes better sense, or if you would like some additional clarification.


Thanks.

[AliGW]

CptTroll - please note that the forum rules state that posts should not be edited after responses have been given. It was enough to add new details in a new post. Please don't do this again as it breaks the flow of the thread. Thanks.

[Glenn Kennedy]

In C2, copied down:

=IF(COUNTIFS(A:A,A2,B:B,"Yes")>=1,"Yes","No")

[CptTroll]

CptTroll - please note that the forum rules state that posts should not be edited after responses have been given. It was enough to add new details in a new post. Please don't do this again as it breaks the flow of the thread. Thanks.

My apologies. I was unaware. I've made sure to read the forum rules now. Thanks for letting me know.

[CptTroll]

In C2, copied down:

=IF(COUNTIFS(A:A,A2,B:B,"Yes")>=1,"Yes","No")

That worked perfectly, Glenn. Thank you so much!

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[CptTroll]

Thread now marked as solved and all contributors have been given reputation.

Thanks everyone for your assistance, it was a great help.

[FlameRetired]

You're welcome. Thanks for the feedback, rep and marking thread Solved.