Solved If Range Contains A Text or Number

August 1, 2016 at 09:02:31
Specs: Windows 7
I'm creating a cell to determine the number of raffle ticket that can be earned by an employee. I would like to have a formula in K1 that if the cell range from A1:J1 contains letters "A" or any numbers, the "true" result is "0" and the "false result is "2"

message edited by ChGatsby


See More: If Range Contains A Text or Number

Report •

#1
August 1, 2016 at 11:49:34
Your requirements are not clear to me.

...if the cell range from A1:J1 contains letters "A"

Do you mean a single A in any cell should return TRUE?
Do you mean an A must be present in every cell to return TRUE?
Do you mean A as part of a word e.g. DAD or as a stand alone "A"?

...or any numbers

Do mean stand alone numbers e.g. 456 or as part of a string e.g. D45t, 323-456, etc?

Please clarify what your data looks like so we can come up with the best solution.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 6, 2016 at 08:44:31
A means any cell that contains letter
Any number means numbers fr 1 to 600

Report •

#3
August 6, 2016 at 19:42:38
✔ Best Answer
Try this:

=IF(OR(COUNTIF(A1:J1,"=A"),SUM(A1:J1)),0,2)

That is actually a shorter version of this:

=IF(OR(COUNTIF(A1:J1,"=A")>0,SUM(A1:J1)>0),0,2)

You don't need the >0 because Excel will consider a 0 result from both the COUNTIF and SUM functions to be FALSE. i.e. two 0's makes the OR FALSE. Anything else makes it not FALSE, which Excel considers to be TRUE.

I know that sounds weird, but in most cases Excel doesn't really determine if a condition is TRUE or FALSE. It actually only determines if the condition is FALSE. If it's not FALSE, it must be TRUE.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question