0

I have a series of unsorted values and I need to identify the top values that sum to at least 50. I want to then use this in a formula somehow to count the number of associated "yes" and "no" values.

Here is some example data:

enter image description here

In case of ties I want to include all the duplicate values in my final result. So in this example the top values are 40, 10 and 10 and my final result would be 1 yes and 2 no's.

I know I can use the LARGE function to get the nth top value so I tried fetching the top values in a separate range and then calculating the running sum:

enter image description here

I could then create a formula to find the row at which the sum reaches >= 50. Now the problem is how to get the yes/no values associated with the numbers and how to deal with ties.

Any ideas would be appreciated! Even better would be a simpler formula without all the intermediate steps.

0

This will find your count:

=SUMPRODUCT(($A$1:$A$7=H2)*(ISNUMBER(MATCH($B$1:$B$7,INDEX(E:E,2):INDEX(E:E,MATCH(50,F:F)),0))))

enter image description here

  • Wow thanks! This almost solved the problem, however, this formula does not work if the sum is not exactly 50. I need the top values that sum to greater than or equal to 50. I solved this by adding another column next to my running sum with the formula = F2 >= 50 (copied down) and then changing your formula so the last MATCH becomes MATCH(TRUE, G:G) – Katia Oct 17 '16 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.