# How to use SUMIF when using Conditional Formatting

1. ## 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.

2. ## Re: How to use SUMIF when using Conditional Formatting

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

Please manually mock up what you want to see.

3. ## Re: How to use SUMIF when using Conditional Formatting

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?

4. ## Re: How to use SUMIF when using Conditional Formatting

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?

5. ## Re: How to use SUMIF when using Conditional Formatting

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.

6. ## Re: How to use SUMIF when using Conditional Formatting

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.

7. ## Re: How to use SUMIF when using Conditional Formatting

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

Formula:
Please Login or Register  to view this content.
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)

8. ## Re: How to use SUMIF when using Conditional Formatting

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.

9. ## Re: How to use SUMIF when using Conditional Formatting

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.

10. ## Re: How to use SUMIF when using Conditional Formatting

Ah ok, I will make sure to do that in future. Sorry about that.

11. ## Re: How to use SUMIF when using Conditional Formatting

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

12. ## Re: How to use SUMIF when using Conditional Formatting

Originally Posted by Fugdkn
Do you know if the row numbers would adjust downwards automatically if I selected multiple rows?
Yes, you can apply the rule to range D2:BR10, for ex., and the calculation will apply the D:BR check against the B value relative to each individual row in the range

On an aside, note that the use of \$C (in the formula) is very deliberate, to avoid circular references, so be sure to keep that reference as-is - i.e. to immediate left of first 'buyer' value.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1