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
Ok
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
noted
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
thanks!
Excelchat Expert
23/04/2018 - 01:47
One moment please
User
23/04/2018 - 01:47
ok
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.
