IF statements and concatenation of conditions

[Bedlam]

I'm trying to create a RAG status for dozens of metrics. Ideally I would like an IF statement that takes account of signage in B and D in combination with values in C and E to assess the value in G to output in H. However it seems you cannot concatenate the signage and values as it thinks the full condition is a string rather than a logical test. Is there a workaround?
=if(g2&b2&c2 ... ) does not work

 

[Alex Blakenburg]

Will this work for you ?

20240403 Countifs Concatenation Bedlam.xlsx
	A	B	C	D	E	F	G	H
1	Metric	Green Threshold	Green Threshold	Red Threshold	Red Threshold		Actual	RAG
2	1	<=	5%	>=	11%		10%	Amber
3	2	>=	90%	<=	75%		90%	Green
4	3	>=	90%	<=	79%		78%	Red
Sheet1

Cell Formulas
Range	Formula
H2:H4	H2	=IF(COUNTIFS(G2,B2&C2),"Green",IF(COUNTIFS(G2,D2&E2),"Red","Amber"))

 

[Bedlam]

Thankyou. It was simpler than I thought

 

[Alex Blakenburg]

You're welcome. Glad I could help.