Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need a formula to check text values in a range and output one of three possible outputs into a cell. ie: If all text values in cell range = A, then Output 1. If all text values in cell range = B, then Output 2. If there is a mix of A and B values in cell range, then Output 3.
Solved by M. J. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 23/04/2018 - 01:15
Hi, are you there?
Excelchat Expert 23/04/2018 - 01:15
Welcome, Thanks for choosing Got It Pro-Excel
Excelchat Expert 23/04/2018 - 01:16
Just to confirm your question, you need an IF analysis for output values based on a range right?
User 23/04/2018 - 01:17
Yes. I need to to be able to output based on all cells in a range being equal to A, all cells in a range being equal to B, or all cells in a range being a mix.
User 23/04/2018 - 01:17
The cells they are referencing are specific text
User 23/04/2018 - 01:17
It will either say "Expectations Met" or "Expectations Not Met"
User 23/04/2018 - 01:18
The three outputs will need to be "All Stores at Expectation", "No Stores Meeting Expectation", or "Some Stores Meeting Expectation"
User 23/04/2018 - 01:18
I got close, but couldnt get the mixed output to work
User 23/04/2018 - 01:18
=IF((COUNTIF($D5:$D7,"Expectations Met")),"All Stores at Expectation"), IF((COUNTIF($D5:$D7,"Expectations Not Met")),"No Stores Meeting Expectation")
User 23/04/2018 - 01:18
Im assuming im way off the mark though
Excelchat Expert 23/04/2018 - 01:21
I see, I'm wondering if we can try to use IF OR combo rather than IF COUNTIF, can you share some sample data in the Untitled Workbook if you don't mind please
User 23/04/2018 - 01:23
So D2 will be the cell requiring the formula
User 23/04/2018 - 01:23
It will analyse D3:D5 and output
Excelchat Expert 23/04/2018 - 01:24
Let me work on some possible solutions, one moment please.
User 23/04/2018 - 01:24
User 23/04/2018 - 01:32
Just as a heads up -
User 23/04/2018 - 01:32
There are some stores that have less than 5 staff
User 23/04/2018 - 01:32
The expectation for them will be less than 5
User 23/04/2018 - 01:32
thats why I didnt base the function on the completion in store
Excelchat Expert 23/04/2018 - 01:32
Understood, thanks for the info
Excelchat Expert 23/04/2018 - 01:34
if Stores are less than 5 staff, how many of them should be tagged "Level 1 Completed" to meet the expectation?
User 23/04/2018 - 01:34
If a store has less than 5 staff it is expected that they ALL complete it
User 23/04/2018 - 01:35
so if they have 2 or 3, that 2 or 3 needs to complete it
Excelchat Expert 23/04/2018 - 01:35
User 23/04/2018 - 01:36
I manually adjust the formula for this as needed (not often), which is why I was hoping to have my formula based on the Expectations Met/Expectations not met output
Excelchat Expert 23/04/2018 - 01:36
I'll do my best to make this work today
User 23/04/2018 - 01:36
That way, it will always output correctly no matter how I adjust my formula for the expectations of the individual storeas
Excelchat Expert 23/04/2018 - 01:37
Will you allow me to add another column?
User 23/04/2018 - 01:37
Of course
Excelchat Expert 23/04/2018 - 01:37
Just wanna test something
Excelchat Expert 23/04/2018 - 01:37
Excelchat Expert 23/04/2018 - 01:47
One moment please
User 23/04/2018 - 01:47
Excelchat Expert 23/04/2018 - 01:59
somethings wrong with online excel
Excelchat Expert 23/04/2018 - 01:59
would mind testing those formulas in Excel please
Excelchat Expert 23/04/2018 - 01:59
it works on my end
Excelchat Expert 23/04/2018 - 02:00
D2 output should be 8
Excelchat Expert 23/04/2018 - 02:03
nvm my fault
Excelchat Expert 23/04/2018 - 02:03
that should work now
Excelchat Expert 23/04/2018 - 02:05
May I know if this solves the question please?
Excelchat Expert 23/04/2018 - 02:06
I had to dedicate a cell with total of those stores with less than or equal to 5 employees
Excelchat Expert 23/04/2018 - 02:07
Cell D2 is the total requirement
Excelchat Expert 23/04/2018 - 02:09
Is there anything else you want to know regarding this issue?
Excelchat Expert 23/04/2018 - 02:10
Thanks for using Got It Pro-Excel
Excelchat Expert 23/04/2018 - 02:10
Please give your kind feedback for our service
Excelchat Expert 23/04/2018 - 02:10
Have a good day

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc