Formula...don't know where to start
I have 3 columns. The cells in each column can have a value of Low, Medium, or High. In a fourth column I want to return a value based the combination of Low, Medium, or High selected in the other three cells. So:
Two or more highs = 4
One high and two mediums = 4
One high, one med, one low = 2
Three mediums = 2
Two mediums and one low = 1
One medium and two lows = 0.5
Three lows = 0.25
This formula far exceeds my abilities. Any takers?
Best Answer

Lets start here...
First we will determine the count of each:
=COUNTIFS([First Column]@row:[Last Column]@row, "High")
=COUNTIFS([First Column]@row:[Last Column]@row, "Medium")
=COUNTIFS([First Column]@row:[Last Column]@row, "Low")
Next we set them up in order to make sure we have everything accounted for:
4:
COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2
or
COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1 AND COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2
2:
COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3
or
COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1 AND COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1
(we will leave that as is  if there is only one high and only one medium, then the third MUST be low so there is no need to clutter things up by specifying that)
1:
COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2 AND COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1
.5:
COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1 AND COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 2
.25:
COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 3
.
Next we will use AND and OR statements to pull each together.
4:
OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2))
2:
OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1))
1:
AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1)
.5:
AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 2)
.25:
COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 3
.
Now we create our IF's and nest them.
If the criteria for 4 is true, output 4:
=IF(criteria for 4, 4,
If 4 is false then check 2. If true, output 2:
=IF(criteria for 4, 4, IF(criteria for 2, 2,
If 2 is false then check 1. If true, output 1:
=IF(criteria for 4, 4, IF(criteria for 2, 2, IF(criteria for 1, 1,
If 1 is false then check .5. If true, output .5:
=IF(criteria for 4, 4, IF(criteria for 2, 2, IF(criteria for 1, 1, IF(criteria for .5, .5,
If .5 is false then check .25. If true, then output .25:
=IF(criteria for 4, 4, IF(criteria for 2, 2, IF(criteria for 1, 1, IF(criteria for .5, .5, IF(criteria for .25, .25)))))
.
Finally we drop in our criteria for each portion:
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(criteria for 2, 2, IF(criteria for 1, 1, IF(criteria for .5, .5, IF(criteria for .25, .25)))))
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1)), 2, IF(criteria for 1, 1, IF(criteria for .5, .5, IF(criteria for .25, .25)))))
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1)), 2, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1), 1, IF(criteria for .5, .5, IF(criteria for .25, .25)))))
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1)), 2, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1), 1, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 2), .5, IF(criteria for .25, .25)))))
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1)), 2, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1), 1, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 2), .5, IF(COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 3, .25)))))
And there you have it.
Answers

Lets start here...
First we will determine the count of each:
=COUNTIFS([First Column]@row:[Last Column]@row, "High")
=COUNTIFS([First Column]@row:[Last Column]@row, "Medium")
=COUNTIFS([First Column]@row:[Last Column]@row, "Low")
Next we set them up in order to make sure we have everything accounted for:
4:
COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2
or
COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1 AND COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2
2:
COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3
or
COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1 AND COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1
(we will leave that as is  if there is only one high and only one medium, then the third MUST be low so there is no need to clutter things up by specifying that)
1:
COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2 AND COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1
.5:
COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1 AND COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 2
.25:
COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 3
.
Next we will use AND and OR statements to pull each together.
4:
OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2))
2:
OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1))
1:
AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1)
.5:
AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 2)
.25:
COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 3
.
Now we create our IF's and nest them.
If the criteria for 4 is true, output 4:
=IF(criteria for 4, 4,
If 4 is false then check 2. If true, output 2:
=IF(criteria for 4, 4, IF(criteria for 2, 2,
If 2 is false then check 1. If true, output 1:
=IF(criteria for 4, 4, IF(criteria for 2, 2, IF(criteria for 1, 1,
If 1 is false then check .5. If true, output .5:
=IF(criteria for 4, 4, IF(criteria for 2, 2, IF(criteria for 1, 1, IF(criteria for .5, .5,
If .5 is false then check .25. If true, then output .25:
=IF(criteria for 4, 4, IF(criteria for 2, 2, IF(criteria for 1, 1, IF(criteria for .5, .5, IF(criteria for .25, .25)))))
.
Finally we drop in our criteria for each portion:
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(criteria for 2, 2, IF(criteria for 1, 1, IF(criteria for .5, .5, IF(criteria for .25, .25)))))
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1)), 2, IF(criteria for 1, 1, IF(criteria for .5, .5, IF(criteria for .25, .25)))))
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1)), 2, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1), 1, IF(criteria for .5, .5, IF(criteria for .25, .25)))))
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1)), 2, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1), 1, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 2), .5, IF(criteria for .25, .25)))))
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1)), 2, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1), 1, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 2), .5, IF(COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 3, .25)))))
And there you have it.

Paul, you are a marvelous human being! I expected this to go unanswered. Not only did you provide the solution, you provided stepbystep guidance on how to get there. I am flabbergasted! Thank you so much. You put so much time and effort helping a complete stranger. Thank you. Thank you.

Happy to help! 👍️
The breakdown actually serves a few purposes...
 It helps you understand exactly how it works and why.
 It helps build out the more complicated final solution.
 It makes trouble shooting much easier.
Help Article Resources
Categories
Check out the Formula Handbook template!