# how to count a variable amount of numbers within a set that sum to a given fraction of the total sum

I have a dataset in the form of 8 columns (separated based on additional variables that aren't directly relevant here) that vary in size each week and each data point is an effectively random positive integer from 0-100 inclusive.

I am trying to design a function to output the minimum number of data points that sum to at least 50% of the total sum of the column - so if a column happened to be the integers 0-100 then the sum would be 5050 and the desired output would be 30. Additionally as I need to repeat this function 8 separate times I need a solution that is compact - in addition to working with the variable data size.

So far I have tried various SUMIFs and COUNTIFs, but trying to set the range using the LARGE function became recursive and spat out error messages. I also tried a few iterative solutions at first - like setting the minimum range by using the total sum divided by the largest value and increasing the range by 1 until it hit the 50% threshold, but given the dataset could run into the thousands this would not meet my compactness requirement.

• You're talking about 8 columns, would you need the result per column? It would be more clear if you add some dummy data and expected result(s) with that. Also not sure why you say 26 would be equal to 50% of 5050.
– P.b
Commented Nov 27, 2023 at 14:11
• @P.b the actual data is arbitrary and was irrelevant to my ability to create a compact solution. I apologise if the parameters for the data were unclear to you. Commented Nov 27, 2023 at 14:27

Random numbers 0..100 are in the column A. Number of data doesn't matter. The result is in the cell `F7`.
``````=LET(a,TOCOL(A:A,1),XMATCH(SUM(a)/2,SCAN(,SORT(a),LAMBDA(b,c,b+c)),1))
Where `a` is declared to only keep the non-empty cell values in column A (you could also use `TOCOL(A:A/ISNUMBER(A:A),2)` to keep number values only, but it works without it as well)).
Then we use XMATCH to find the first value equal to or greater than the sum of `a` divided by 2 (=50%) in the SCAN of the SUM of the sorted values of `a` from smallest to largest value (if you included text, these will result in errors, but are in the end of the error, prior to the match being found).