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?”

    11 months 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
    11 months 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.

    11 months ago

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

    Kazoo2U
    11 months 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.

Leave a New Comment Related to "How Do I Sum 5 Entries Only If Number Of Entries Is Greater Than 3?"

:
:
:
0/500 (500 character limit -- please state your comment succinctly and do not try to get around this limit by posting two comments)