counting cells containing specific text

ceds

New Member
Joined
Jul 30, 2010
Messages
27
Hi,

I am trying to write a formula so that if three cells contain specific text, a number one will be put in a certain column.

For example:
Cell A1 contains an A
Cell B1 contains a B
Cell C1 contains a B

What formula would I need so that if this pattern exists a number 1 would appear in cell E1 for example?

Many thanks if you can help!
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
I'd like to think there's a more elegant way (probably using array formula) but this appears to do the job:

=--AND(NOT(ISERROR(FIND("A",A1))),NOT(ISERROR(FIND("B",B1))),NOT(ISERROR(FIND("B",C1))))

If there's a match you get a 1, if all 3 conditions are not met, a zero is returned.

HTH
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hi ceds, welcome to the board.
I doubt your example is as simple as the real desire, but how about this to start? (In E1)
Code:
=(A1="A")*(B1="B")*(C1="B")
This will return a 1 in E1 if all are true, or a zero if all are not.

That help at all?
 

ceds

New Member
Joined
Jul 30, 2010
Messages
27
That's great, thanks both of you - both solutions will definitely help with what I'm looking to do.

Thanks again!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,505
Messages
5,529,265
Members
409,859
Latest member
emperorgenghiskhan
Top