How to sum up items until a minimum threshold is met?

Hi all,

I'm wondering if anyone can help. I'm trying to sum up the values in a column until a certain value is met, however, I need it based on the lines of "until at least the threshold is met".

Within my data, I have a list of items (Column A), each with a sales value (column B) and a representation of what % each item accounts for (column C). I need to display in column D which of these items sit within the top 70%, however, the first 5 items make up 69% and the next item take the total up to 78% - meaning the 6th item appears as a "No", when I actually need it to say yes. I've shown the formula that I've currently been trying to use and having issues with.

I'm not sure if this makes sense, but hopefully my example gives a bit more context. I just need to sum all the items in column C until at least 70% is reached and those items to display a "Y" in column D.

Is this possible?

Thanks very much !

Try

in D5

=IF(SUM($C$5:C5)<$E$1,"Y",IF(SUM($C4:C$5)<$E$1,"Y","N"))

Hi JohnTopley

Thank you so much for coming back to me so quickly with this ! In the example, this works perfectly.

I tried to apply it to my actual data and I hit a bit of a snag (I can post this in a separate thread if that is more appropriate?).

My actual data is set up a little differently as I'm working off of a pivot where I have different items across different departments, and I'm adding to each item's individual %, a base level % I already have. When I add in the logic of your formula, I get the result I'd expect to see until the last "Y", and then I just get #value errors.

I can't see though what is causing the value errors.

The reason for trying to set it up this way is that I have over 127,000 rows in my data, so I need a formula I can drag down which looks at items in relation to the department they sit in (otherwise, I have to ammend the formula for each department to only look at the values in column D per department). e.g. I need to look at the %'s of items in the "fruit" department, seperate to the %'s of items in the veg department.

Hi John,

Thanks so much again for your time looking into this. I can see that the formula(s) solve the Value errors when you manually enter them into the start of each new section of the pivot. I guess the issue I have is with 1000's of rows, adjusting the formula references at each section is going to take me a while, but I can't really see any other way around it, so guess I'm in for a long night!

Thanks so much again, I really do appreciate your time.

A simpler formula =IF(SUMIFS(D$5:D5,A$5:A5,A5)+IFERROR(VLOOKUP(A5,$M$3:$O$4,2,0),0)<=$H$2,"Y","N")

Thank you both for your help with this! The formulas did the job - though needed a bit of tweaking as it was thinking that the subtotal rows in the pivot contained data which should be factoured in, but this has saved me hours of work, so thanks so much !!!!

You're welcome and thank you for the rep.