Condition a cell result based on rows value

[Dani_LobP]

Hi all,

I'm struggling to make a condition to a value to show a result based on the value of rows below in the same column.

The idea is to give either R, A or G result, based on what is below.

So if i have X amount of rows below, and each one has different values in combinations of R, A and G, the top row will display the summary lets say.
Is some kind of take worst case scenario, since R is Red, A amber and G green.

Condition is as follow:
- as long as there is 1 R in the rows, main result will be R.
- if there is no R at all, but they are mix of A and G, then main result will be A.
- if ALL are G, then main result will be G.

	R	#N/A -> should be A	G
ex1	G	G	G
ex2	A	A	G
ex3	G	G	G
ex4	G	G	G
ex5	A	A	G
ex6	A	A	G
ex7	G	G	G
ex8	R	A	G
ex9	A	A	G
ex10	R	G	G

Code i am using on the fist top row:

=IF(AND(B2="G",B3="G",B4="G",B5="G",B6="G",B7="G",B8="G",B9="G",B10="G",B11="G"),
"G",
IF(MATCH("R",B2:B11,0),
"R",
IF(OR(B2="A",B3="A",B4="A",B5="A",B6="A",B7="A",B8="A",B9="A",B10="A",B11="A"),"A")))

For some reason I manage to make it work for when there is at least 1 R, and when all is G.. A is what i don't seem to understand why is not working.

Thanks in advance!

 

[etaf]

did you want VBA code
a function
using Countif

=Countif($B$2:$B$100, "R")>0
=Countif($B$2:$B$100, "A")>0
combined in an IF

=IF(COUNTIF($B$2:$B$100,"R")>0,"R",IF(COUNTIF($B$2:$B$100,"A")>0,"A","G")

if the range is blank , then it will be green
But we could avoid that if necessary

Change the range to match you data

Book1
	B	C	D
1	R	A	G
2	a	a	g
3	r	g	g
4	g	a	g
Sheet1

Cell Formulas
Range	Formula
B1	B1	=IF(COUNTIF($B$2:$B$100,"R")>0,"R",IF(COUNTIF($B$2:$B$100,"A")>0,"A","G") )
C1	C1	=IF(COUNTIF($C$2:$C$100,"R")>0,"R",IF(COUNTIF($C$2:$C$100,"A")>0,"A","G") )
D1	D1	=IF(COUNTIF($D$2:$D$100,"R")>0,"R",IF(COUNTIF($D$2:$D$100,"A")>0,"A","G") )

 

[Dani_LobP]

did you want VBA code
a function
using Countif

=Countif($B$2:$B$100, "R")>0
=Countif($B$2:$B$100, "A")>0
combined in an IF

=IF(COUNTIF($B$2:$B$100,"R")>0,"R",IF(COUNTIF($B$2:$B$100,"A")>0,"A","G")

if the range is blank , then it will be green
But we could avoid that if necessary

Change the range to match you data

Book1
	B	C	D
1	R	A	G
2	a	a	g
3	r	g	g
4	g	a	g
Sheet1

Cell Formulas
Range	Formula
B1	B1	=IF(COUNTIF($B$2:$B$100,"R")>0,"R",IF(COUNTIF($B$2:$B$100,"A")>0,"A","G") )
C1	C1	=IF(COUNTIF($C$2:$C$100,"R")>0,"R",IF(COUNTIF($C$2:$C$100,"A")>0,"A","G") )
D1	D1	=IF(COUNTIF($D$2:$D$100,"R")>0,"R",IF(COUNTIF($D$2:$D$100,"A")>0,"A","G") )

No, was not looking for VBA but just a simple formula, what you shared is somehow working better than the path I chose :D thanks a lot for the help!

 

[etaf]

you are welcome