**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.*