+ Reply to Thread
Results 1 to 12 of 12

How to use SUMIF when using Conditional Formatting

  1. #1
    Registered User
    Join Date
    07-29-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    36

    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.
    Attached Files Attached Files
    Last edited by Fugdkn; 08-01-2020 at 04:20 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,270

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    07-29-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    36

    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?
    Attached Files Attached Files
    Last edited by AliGW; 08-01-2020 at 04:27 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,270

    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. #5
    Registered User
    Join Date
    07-29-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    36

    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.
    Last edited by AliGW; 08-01-2020 at 07:32 AM. Reason: Please don't quote unnecessarily!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,270

    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. #7
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,888

    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: copy to clipboard
    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)
    Last edited by XLent; 08-01-2020 at 08:10 AM.

  8. #8
    Registered User
    Join Date
    07-29-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    36

    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.
    Last edited by AliGW; 08-01-2020 at 09:39 AM. Reason: Please don't quote unnecessarily!

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,270

    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. #10
    Registered User
    Join Date
    07-29-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    36

    Re: How to use SUMIF when using Conditional Formatting

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

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,270

    Re: How to use SUMIF when using Conditional Formatting

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

  12. #12
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,888

    Re: How to use SUMIF when using Conditional Formatting

    Quote Originally Posted by Fugdkn View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help with slow worksheet (conditional formatting & sumif formulas)
    By LordClick in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-25-2017, 01:44 AM
  2. Sumif and Conditional Formatting
    By kgkgkg9009 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-25-2017, 07:59 PM
  3. Conditional Formatting Formula IF or SUMIF
    By Lawman1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-06-2017, 10:14 AM
  4. [SOLVED] Expert Needed: Conditional Formatting and SumIF/VlookUp issue (Last row)
    By newtotheblock in forum Excel General
    Replies: 4
    Last Post: 01-09-2015, 10:06 PM
  5. [SOLVED] Conditional Formatting Formula - if(and(sumif.....
    By mmaya4 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-23-2014, 02:47 AM
  6. [SOLVED] sumif with conditional formatting
    By msmathad in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-17-2013, 02:16 PM
  7. [SOLVED] Countif or Sumif based on Conditional Formatting Color
    By Kune in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 6
    Last Post: 10-07-2012, 08:47 AM

Tags for this Thread

Bookmarks

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