counting lowest or the 2 lowest numbersonly if there ar at least 5 numbers

Hi i want to have a cell that records the lowest number in a range or the sum of the lowest 2 numbers in a row but ony if there are more than 5 numbers.

Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 EXCLUDE
20 30 40 50 0 NO EXCLUDE AS LESS THAN 5 NUMBERS
40 30 40 50 45 9 9 9 EXCLUDED AS MORE THAN 5 NUMBERS
30 30 40 50 45 9 11 20 EXCLUDED AS MORE THAN 5 NUMBERS AND THE LOWEST 2 (9=+11)

I need a formula that will enter 20 in a cell as there are more than 5 numbers and the sum of the lowest 2 numbers is 20. To be more precise i need to enter a value for the 6th 7th etc numbers in a row.

In the image attached you can see that the third row is wrong. I was using =IF(COUNT(H17:Q17)>5,MIN(H17:Q17),0) but this will only count the lowest.

Thank you

How about

Formula:

Please Login or Register  to view this content.

Try this:

=IF(COUNT(H17:Q17)=6,MIN(H17:Q17),IF(COUNT(H17:Q17)>6,SUM(SMALL(H17:Q17,{1,2})),0))

Hi

It counts the lowest 2 numbers even if there are only 5 numbers. I need it to disregard the highest 5 numbers and sum the remainder.
Thanks

Not sure who you are talking to, but neither formula does that.

It works to a point. But i need to keeep the 5 highest numbers and sum the total of the remainder.

Can the function be amended to sum the numbers after the top 5

Who are you talking to?

I was responding to your post

In that case can you supply a workbook showing where it is going wrong & what the correct answers are.