0

Is there a way to apply conditional formatting or another formula on a multiple range of cells to determine if the combined sum in a range of cells is equal to or less than a value in a single cell?

The first attachment is a screenshot of the forecast report I am looking to apply this formula to. The highlighted green/red cells is what I am looking for the formula to do automatically for all items.

Screenshot of how I am looking for formula to work:
Screenshot of how I am looking for formula to work

The second attachment is the current conditional formatting rule I have but it is not working properly and I cant seem to figure out what I am doing wrong.

Screenshot of current formula I have but it is wrong:
Screenshot of current formula I have but it is wrong

For example, I need to know how far out the current Quantity on Hand for a given part covers demand for.

Ex.) Item SC688 (row 2)-- Qty on hand is 13,012. This only covers demand for the months of June and July. I want to use a formula to automatically highlight the months current on hand qty covers through (06/2023 and 07/2023) and highlight in red the remaining months (08/2023 - 06/2024) that current stock wouldn't cover. Can this be done with conditional formatting or is there another formula I need to use?

I appreciate any help you can provide!

Improve this question
1
  • 1
    ,,,, it's as simple as U are trying (2nd Screen Shot) ,,,, since the screen shot data/formula is not readable,,, so that I can't find the reason ,, please share the formula what U have used so far ,,, then I'll try to fix it ☺
    – Rajesh Sinha
    Commented Jun 20, 2023 at 3:06

1 Answer 1

Reset to default
1

You have a minor error in the formula which is causing the formatting to applied not based on what you want.

=$E$2<SUM($H2:H2) (specifically the $E$2) means an absolute cell reference to always (for all rows) makes the comparison based on the Qty On Hand of the first row. In order to allow the cell reference (to compare to) to be a relative reference (changed to the Qty On Hand of the current row) you'd need to have it as $E2 with the 2 being a relative reference.

So use for the red:

=$E2<SUM($H2:H2)

And for the green (also improving here adding the equals, that if it matches exactly, it should still be green and not remain white):

=$E2>=SUM($H2:H2)

enter image description here

Improve this answer
2
  • 1
    I would try to avoid two mutually exclusive conditional formats. This makes Excel work out both cases, where only one is needed. In other words, give all the cells a background colour of green*, and just apply the condition that turns them red (or the other way round). Also consider the edge case where the total = Qty On Hand and make sure the colour is what you would want to see, or maybe add a condition for this as "amber" (yellow) to indicate that this month's order is fulfillable, but there will be zero stock left for next month. *Also, make the green a lot lighter so the red "pops".
    – AdamV
    Commented Jun 20, 2023 at 11:56
  • 1
    @Yisroel Tech thanks for your help it looks like this worked!!
    – Juan P.
    Commented Jun 20, 2023 at 13:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .