If cell contains then function

[AmyTaylor]

Hope someone can help with this function query I have:

I have the following function in cell F18 of my total sheet, it is a
concatenation of 4 cells on sheets T10, T6, T7 and T9:
CONCATENATE('T10'!F18&'T6'!F18&'T7'!F18&'T9'!F18)
The answer will be a combination of A,B,C,D or E.
What I would like is this, if possible:
If the concatenation contains mostly A or B then it is a "green", it is
contains no As then it is a "amber", if it contains mostly D or E then
it is a red.
If we need to specify the actual number of characters, then it would
be:
3 or more As or Bs and it is a "green", 3 or more Cs then it is "amber"
and 3 or more D or E then its a "red".

All help greatly appreciated
Los of love
Amy xx

 

[systemx]

Hi Amy,

Someone else may know a better way to do this. But here is the
long-winded way!

Lets say cell A1 contains your text string (ABCDE)

In cell B1 use =LEFT(A1,1)
In cell C1 use =MID(A1,2,1)
In cell D1 use =MID(A1,3,1)
In cell E1 use =MID(A1,4,1)
In cell F1 use =MID(A1,5,1)

To split the concantenated string into individual cells.

Then

In E1 =COUNTIF(B1:F1,"A")
In F1 =COUNTIF(B1:F1,"B")
In G1 =COUNTIF(B1:F1,"C")
In H1 =COUNTIF(B1:F1,"D")
In I1=COUNTIF(B1:F1,"E")

This will give you a raw count of how many A's, B's, C's, D's and E's.

Last but not least....the final cell....

=IF(E1+F1>3,"green",IF(G1>3,"amber,IF(H1+I1>3,"red",0)))

Of course this will only return the colour as a text string inside your
calculation cell. Simply replace 'green', 'amber' and 'red' with
whatever values you want, then use conditional formatting to colour
appropriately.

There is probably a much smarter way to do this.....hope someone finds
it for you

Regards

Rob

 

[Guest]

For the first condition

=(LEN(F18)-LEN(SUBSTITUTE(F18,"a",""))+LEN(F18)-LEN(SUBSTITUTE(F18,"b","")))>3

 

[Dav]

If the cell you are interested in is g3 enter the following as formulas
in conditional formating

Condition 1
=LEN(G3)-LEN(SUBSTITUTE((SUBSTITUTE(G3,"a","")),"b",""))>=3 choose
green as colour

Condition 2 =LEN(G3)-LEN(SUBSTITUTE((SUBSTITUTE(G3,"d","")),"e",""))>=3
choose amber

Condition 3 =LEN(G3)-LEN(SUBSTITUTE((SUBSTITUTE(G3,"d","")),"e",""))>=3
choose red

Regards

Dav