Excel - IF Function Problem - Expert Solution

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

I want to display a range of cells if they meet a specific criteria. I want to display a name IF it does not equal one of three options AND there are any numbers contained in a range of cells
Solved by G. F. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 04/04/2018 - 07:57
Hello, Welcome to Got It Pro
Excelchat Expert 04/04/2018 - 07:58
Are you there?
User 04/04/2018 - 07:58
yes
Excelchat Expert 04/04/2018 - 07:59
How may I help you?
User 04/04/2018 - 08:00
I want to display all information on a customers order, based on several conditions but I am unsure how to do so
Excelchat Expert 04/04/2018 - 08:00
Can you please tell me the conditions?
Excelchat Expert 04/04/2018 - 08:01
and what information you want to display?
Excelchat Expert 04/04/2018 - 08:03
Hello?
User 04/04/2018 - 08:03
IF Column A does not contain customer A.B,C
Excelchat Expert 04/04/2018 - 08:04
Yes?
User 04/04/2018 - 08:04
AND there is a number in any cells between B-G
User 04/04/2018 - 08:05
then display all of the information in row 2,3,4 etc
Excelchat Expert 04/04/2018 - 08:06
you mean to say that if column A is blank and there is any number from B2 to G7 then it should display the row 2,3,4 data?
Excelchat Expert 04/04/2018 - 08:06
Right?
User 04/04/2018 - 08:08
no so to take customer A as an example, if A2 does not contain either A,B or C AND there is a number in any of the cells B2-G2 then display the information in cells A2-G2
User 04/04/2018 - 08:08
so in the second sheet, row 2 would display as False as it would not meet all conditions, as A2 does contain A
Excelchat Expert 04/04/2018 - 08:09
What's A? A2 has A in it actually.
User 04/04/2018 - 08:09
however, row 5 would display information as it meets both conditions, there is not an A,B or C in A5 AND there is a number in one of the specified colums
Excelchat Expert 04/04/2018 - 08:10
you wrote that " if A2 does not contain either A,B or C " .. But how A2 is supposed to have A,B,C.?
User 04/04/2018 - 08:10
yes because the same formula will be applied to each row
User 04/04/2018 - 08:11
so if there is an A then it will simply display false
User 04/04/2018 - 08:11
but row 5,6,7 would not display false as they do not contain the letters, A,B or C
Excelchat Expert 04/04/2018 - 08:12
Row 7 is customer F and it has value in C thats is 7
Excelchat Expert 04/04/2018 - 08:12
I am not getting you
Excelchat Expert 04/04/2018 - 08:13
It looks like you are confusing the column Alphabets and customer names alphabets
User 04/04/2018 - 08:13
no i am not
Excelchat Expert 04/04/2018 - 08:13
Can you please change this data so it would be more clear and easy to explain?
User 04/04/2018 - 08:14
easier now
Excelchat Expert 04/04/2018 - 08:14
yes now tell me
User 04/04/2018 - 08:16
if any cell in column A does not equal Chris, Bob or Sarah AND there are any numbers in B-G, then display the information in the corresponding row
User 04/04/2018 - 08:20
I have added a formula to see if that is any more explanatory, of course this does not actually work
Excelchat Expert 04/04/2018 - 08:21
I have to ask you one question.
Excelchat Expert 04/04/2018 - 08:22
If column A does not equal chris, bob and sarah then whose information you want to display?
User 04/04/2018 - 08:22
just a false response
Excelchat Expert 04/04/2018 - 08:23
They all will be false then
User 04/04/2018 - 08:24
no they wont, row 5,6 and 7 do not contain Christopher, Bob, or Sarah
Excelchat Expert 04/04/2018 - 08:24
Okay. Let me check
Excelchat Expert 04/04/2018 - 08:28
Check column J
Excelchat Expert 04/04/2018 - 08:28
sorry Column H
Excelchat Expert 04/04/2018 - 08:29
Actually number condition will give all False because number is coming in all the columns from B2 TO G7
User 04/04/2018 - 08:30
yes the formula is correct for the name of customer, but I need it to also test the condition of the numbers
Excelchat Expert 04/04/2018 - 08:31
If you will add the number condition, it will all show FALSE
User 04/04/2018 - 08:31
why?
Excelchat Expert 04/04/2018 - 08:32
Because number is coming in all the columns
User 04/04/2018 - 08:33
yes but in row 1-3 it does suit the primary condition
User 04/04/2018 - 08:34
does not*
Excelchat Expert 04/04/2018 - 08:36
It does, there are numbers in Column B,C,G in row 2,3,4
User 04/04/2018 - 08:37
but the primary condition is that it is not Chris, Bob or Sarah and it is
Excelchat Expert 04/04/2018 - 08:37
You want to put an OR between the numbers and name condition?
Excelchat Expert 04/04/2018 - 08:38
because u said that its an AND condition and in AND if both are met then it displays the result
User 04/04/2018 - 08:38
no an AND
User 04/04/2018 - 08:38
yes thats what I want
User 04/04/2018 - 08:39
but if the conditions are met I want it to display the information in the corresponding sell, not TRUE
User 04/04/2018 - 08:39
the formula in column I doesn't function correctly
User 04/04/2018 - 08:40
based on the formula row 5,6 and 7 should display as TRUE
Excelchat Expert 04/04/2018 - 08:41
Okay let me see.
Excelchat Expert 04/04/2018 - 08:42
There are numbers in column B,C,D,E,F,G
User 04/04/2018 - 08:45
yes
Excelchat Expert 04/04/2018 - 08:47
Actually the problem is that ISNUMBER function works for one value only
Excelchat Expert 04/04/2018 - 08:48
we are putting a range so its not giving the correct result
User 04/04/2018 - 08:50
ok
Excelchat Expert 04/04/2018 - 08:54
Please check
Excelchat Expert 04/04/2018 - 08:56
The session time is about to end. Please do leave your kind rating after session ends.
Excelchat Expert 04/04/2018 - 08:56
Thank you for using Got It Pro. Have a nice day :)
Excelchat Expert 04/04/2018 - 08:57
=IF(AND(A$2:A$7<>"Chris",A$2:A$7<>"Bob",A$2:A$7<>"Sarah",ISNUMBER(B$2),ISNUMBER(C$4),ISNUMBER(G$3),ISNUMBER(D$5),ISNUMBER(E$6),ISNUMBER(C$7)),TRUE,FALSE)

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.