May 25 2022 01:56 PM
I have a string of numbers. I am trying to auto calculate the lowest 10 of the last 20 out of a string of 50 numbers.
I have used the following formula =SUMPRODUCT(SMALL(Q4:AV4,{1,2,3,4,5,6,7,8,9,10})) to get my answer but by doing this I have to select the last 20. I want to be able to select all 50 then have excel look at the last 20 and pick the low 10 out of that list and give me the total of those 10. I am pretty sure I need to do some sort of lookup but not sure how to write that. TIA