How to use SUMIF when using Conditional Formatting

Hey. So what I am basically trying to do is find a way to make a rule that ensures that we fulfil (highlight) orders up to the amount of stock we have, but no more than that.

Another restriction is that the orders won't always be in numerical order, in fact, it will basically always be random. We could sort them, but there are going to be 100s or even 1000s of rows, so sorting the column order based on the buyer's order volume for a given row would take far too long (it would be easier to do it manually and just click on the colours myself). Hopefully the attached file can give more insight as to exactly what I am trying to do. Thanks.

Unfortunately it does not, no, as there is no highlighting there.

Please manually mock up what you want to see.

Sorry about that. So, what I have done in the new file is I have basically, fulfilled 144 + 72 = 216 worth of orders,
as that is the maximum amount of orders that can be fulfilled (based on stock which 216).

I highlighted Buyer 10 and Buyer and Buyer 17's orders for Product X. I am trying to find out
how I could do this with a conditional formatting rule (if this is possible) + if I can do this
without having to sort the row from smallest to largest or anything like that. Is that a bit clearer?

That's a very convenient example. What do you expect to happen if there is no exact match to the stock in hand? Which cells would be highlighted then?

If there is not an exact match (e.g. we have two orders of 144, but we only have 216 in stock), then we would just fulfil one of the 144 orders, and that would be it.

OK - thanks for the clarification. I am absolutely sure there is a way, however I am not sure how to do it. Someone else will advise.

with D2 the active cell, you can apply the following rule (and apply to D2:BR2)

Formula:

=IF(D2,($B2-SUMIFS($C2:C2,$C$1:C$1,"Buyer*",$C2:C2,"<="&$B2,$C2:C2,">="&D2))>=D2)

set format as required

the above will find items left to right, in that order, until such time as the stock requirement is filled, or there are no other options to complete it.

edit: if you end up with scenarios where you have stock of say 150, and available values of 144, 72 and 78 -- the above will format 144, rather than 72 & 78
so, if it transpires that you want the 'optimal' combination (i.e. combination closest to total) then things get a whole lot more complicated - to point of not really being feasible (in XL)

That looks very interesting! I will study it later today or tomorrow and make sure I understand it properly. Do you know if the row numbers would adjust downwards automatically if I selected multiple rows?

Also, the stock will always be in the same multiple as the orders (e.g. 72), so the 150 scenario shouldn't ever happen (or the buyer of 78). It would be best to get the combination closest to total, but if this will allow me to drag down and use conditional formatting on a massive range of cells, then the time saving is worth the optimality.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

No worries - it just makes the thread easier to read.