Forum Discussion
Solved
Excel - Max Limit with Conditions
I have a spreadsheet with several questions on it that can be rated into several categories. For example: Inadequate Below Average Average Above Average Superior Question 1 x ...
Use the formula
=SUMPRODUCT(IF(COUNTIF(B3:C7,"<>"),IF(B1:F1>D1,D1,B1:F1),B1:F1)*(B3:F7<>""))
It won't matter whether you enter the description or x or something else in the cells - all that matters is that they aren't empty.
Can you do that with the actual values instead of the "x" placeholders? Sorry, my example should have reflected that.
- Or, probably even better... if those cell are not blank?
Use the formula
=SUMPRODUCT(IF(COUNTIF(B3:C7,"<>"),IF(B1:F1>D1,D1,B1:F1),B1:F1)*(B3:F7<>""))
It won't matter whether you enter the description or x or something else in the cells - all that matters is that they aren't empty.