MacMost Q&A Forum • View All Forum QuestionsAsk a Question

How Do I Sum 5 Entries Only If Number Of Entries Is Greater Than 3?

Need to create a formula that allows me to do the following:
1. Add 5 numbers, then subtract the max of those numbers. {I can do this already}
2. Only compute value in #1 if the number of entries is greater than 3.

Here are examples of what I want to do:
A. 2+3+4+5+999 = 14
B. 2+4+6+8+3 = 15
C. 2+ +5+9+ = [creates blank space]
D. 4+ + + +. = [creates blank space]
—–
Kazoo2U

Comments: 4 Responses to “How Do I Sum 5 Entries Only If Number Of Entries Is Greater Than 3?”

    4 years ago

    Are these cells? So in example A, are these say cells B2 through F2? So the formula you have now is in G2 and it is =B2+C2+D2+E2+F2?

    If that is the case, then just use ISBLANK to determine if a cell is empty. You can add those together to get the number of empty cells. Then use an IF statement to test to see if there are more than 2 blank cells and put a blank into the result. Otherwise, sum up the five cells.

    =IF(ISBLANK(B2)+ISBLANK(C2)+ISBLANK(D2)+ISBLANK(E2)+ISBLANK(F2)>2,"",SUM(B2:F2))

    Kazoo2U
    4 years ago

    Your formula works! Thanks for the quick response.

    My cells are in a column, but I figured out how to insert the new cells into your formula.

    Once I have all 5 entries added together, now have to figure how to subtract the max entry to get the best 4 of 5 values.

    4 years ago

    Kazoo2U: Just use the MAX function to get that. So -MAX(B2:F2) after the SUM.

    Kazoo2U
    4 years ago

    Again, thanks for the quick response.

    After a little fiddling, your formula works.

    Among the best, if not the best, customer support on the web.

Comments Closed.