Forum Discussion
Finding the 5 least numbers from cells with formulas
Hi everyone, I have the following challenge:
I am trying to get the smallest 5 numbers to be summed up from an array of cells. The challenge is that when I use formulas such as SUMPRODUCT or SMALL, I get an #NUM! or #VALUE! error. The cells in the array all have the following formula to get the desired number, =IF(G7>=80,"1",IF(G7>=75,"2",IF(G7>=70,"3",IF(G7>=65,"4",IF(G7>=60,"5",IF(G7>=55,"6",IF(G7>=50,"7",IF(G7>=45,"8",IF(G7>=1,"9",IF(G7>=0,"-"))))))))))
How can I get to sum up the 5 smallest numbers, above 0 without the error(s)?
- Remove the double quotes.
8 Replies
Depends what you'd like to sum. For such sample
if small 5 from and you are on Excel 365 or 2021 it could be
=SUM( SMALL( FILTER( G7:G17, G7:G17), SEQUENCE(5)) )If next column, it shall be calculated as numbers, not texts
=LOOKUP( G7, {0,1,45,50,55,60,65,70,75,80}, {0,9,8,7,6,5,4,3,2,1})- Remove the double quotes.
- WOW! Such a small fix but it works! Thank you Detlef_Lewin for the help.
- Double quotes around the numbers makes them text. Some functions ignore text (like SMALL()), and some throw an error.
=SUMPRODUCT(AGGREGATE(15,6,IF(A1:A25>0,A1:A25),{1,2,3,4,5}))
I tried this formula and it seems to work. If you don't work with Office365 or 2021 you have to enter the formula with ctrl+shift+enter.
- @Quaruple_Pawn, because of the double quotes, your formula was not working but as soon as I removed them, it worked like a charm.
Thank you for the massive help! - Hi OliverScheurich
Thank you for the prompt response. I have tried your formula and it shows the #VALUE! error. If i press ctrl+shift+enter, it shows the #NUM! error.That' s surprising as it works in my spreadsheet.
Can you attach a file of your spreadsheet where i can see the formula you entered and the range of data which you want to sum.
