If formula help please

[PinkUnicorn]

Hi guys
This is my first post and I'm really stuck on a formula. any help would be really appreciated.

I've got a column of results from various checks across my spreadsheet, and each of these checks will come back with a result of High, Medium or blank.

What I'm trying to do is create a formula to look at that column and return a single result
if there is a High anywhere in the column I want it to say "High"
If there isn't a high, but there is a medium anywhere in the column I want it to say "medium"
And if there are no High's or Mediums (so all are blank returns) I want it to say 'Standard:

This is where I've got to so far
=IF(J5:J13="","Standard",IF(J5:J13="Medium","Medium",IF(J5:J13="High","High")))

But it only seems to be looking for what is entered in the first cell (J5) and not the others.

I tried it round the other way, so looking for the Highs first but that didn't seem to work at all!

All help greatly appreciated!

 

[Joe4]

Welcome to the Board!

You cannot check of a value of a whole range like that.

Try this instead:

=IF(COUNTIF(J5:J13,"High")>0,"High",IF(COUNTIF(J5:J13,"Medium")>0,"Medium","Standard"))

 

[PinkUnicorn]

Fantastic, thank you.

I was searching the 'Count if' scenarios but I simply did not get them!!

Thank you so much - it worked perfectly.

 

[Joe4]

You are welcome. Glad I was able to help.

Here is a good write-up on COUNTIF: https://www.techonthenet.com/excel/formulas/countif.php