Conditional formatting based on sum of adjacent cells values and specific data

**joey1** · 11-10-2017, 04:51 PM

I have a table where the values are quantities of ingredients that need to be used up, prioritized by conditional format depending on the dates values (first row dates). However i need the formula to take into consideration the maximum amount of each ingredient required (last column data), so only highlight the amounts than are required.

The formula i have is working but
a) It does not work if the sum exceeds what is required. This is what I’m trying to add: OR(SUM($C$2:D2)<=$P2, P2>SUM($C2:C2)) but not sure how.

b) Struggling to get it applied properly to multiple rows and columns.

Example attached.

**63falcondude** · 11-10-2017, 04:53 PM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**joey1** · 11-11-2017, 06:00 AM

Manage to solve it using simply SUM($B2:B2)<$P2 , whereby each new cell sums all previous cells and ensure they below the maxium allowed value and if they are, then the cell changes colour.

The only minor problem i have is the starting reference cells in B column have product codes in them and are not relevant to the formula. Is there any solution other than inserting a blank column.

**63falcondude** · 11-11-2017, 10:46 AM

In the sample that you attached, column P is blank. Did you mean column O?

Also, please update your sample with the desired result (bold part from post #2) showing.

**joey1** · 11-11-2017, 02:47 PM

sorry, yes in the example its column Q. I was looking at my own sheet.

Have attached updated sheet, everything working fine. Have the blank C column inserted, if no other option, will have to go with that.

**63falcondude** · 11-12-2017, 01:11 PM

Try this (after deleting that empty column so that your data goes from A1:O6):

Highlight C2:J6 > Conditional Formatting > New Rule > Use a formula

=AND(OR(COLUMNS($A$1:A$1)=1,SUM($B2:B2)<$O2),C$1 < TODAY())
Format: Fill red > OK > OK

=AND(OR(COLUMNS($A$1:A$1)=1,SUM($B2:B2)<$O2),C$1 = TODAY())
Format: Fill yellow > OK > OK

=AND(OR(COLUMNS($A$1:A$1)=1,SUM($B2:B2)<$O2),C$1 > TODAY())
Format: Fill green > OK > OK

It doesn't matter that the sum range is starting with column B since the SUM function only looks at numbers.

**joey1** · 11-12-2017, 07:23 PM

That works but it ignores if any of the cells are blank other than column O.

I need to have AND(C2>0,OR($O2<>"", $N2<>"") in the formula.

**JeteMc** · 11-13-2017, 01:23 PM

See if this application of the inverse works.
Selecting the range C2:J2 this should be the first rule: =AND(C2=0,OR($O2="",$N2=""))
The 'Stop if True' box for this rule should be checked and No Format Set should show in the format window.
If this doesn't produce the formatting results you are looking for it will help if you upload a sample (manually mocked up) showing what you want.
Let us know if you have any questions.

**joey1** · 11-14-2017, 06:58 AM

JeteMc, I have used =OR(C2=0,$n2=""), which seems to work as I wanted but the blank column issue remains as column b has numerical values in it, which are used to identify each product. This is resulting in c column values always being CF due to column b values.

**JeteMc** · 11-14-2017, 08:50 AM

I think that it would help if an updated version of the spreadsheet were to be uploaded. Here is one that shows the application of 63falcondude's rules from post #6 and your rule from post #9. Please tell us what isn't happening that should happen or visa versa.

**joey1** · 11-14-2017, 02:42 PM

If you look on that sheet, for example: cell o5 total is 31 but the cells that are CF(cell c5, d5, and e5), only add up to only 30. The problem is cell d5 is not being CF. The reason for this is that the formula is using values in b5 to work out the total sum. As you delete the values in b5, cell f5 is highlighted.

Hope this helps explain things.

**JeteMc** · 11-14-2017, 04:41 PM

I gotcha. OK, in the formulas for red, yellow and green formatting, modify the part that currently reads: SUM($B2:B2)<$O2
so that it will read: SUM($B2:B2)-$B2<$O2
Let us know if you have any questions.

**joey1** · 11-14-2017, 06:24 PM

That did the job. Thanks 63falcondude and JeteMc.

**JeteMc** · 11-14-2017, 06:53 PM

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

Conditional formatting based on sum of adjacent cells values and specific data

LinkBack

Thread Tools

Rate This Thread

Display

Conditional formatting based on sum of adjacent cells values and specific data

Re: Conditional formatting based on sum of adjacent cells values and specific d

Re: Conditional formatting based on sum of adjacent cells values and specific data

Re: Conditional formatting based on sum of adjacent cells values and specific data

Re: Conditional formatting based on sum of adjacent cells values and specific data

Re: Conditional formatting based on sum of adjacent cells values and specific data

Re: Conditional formatting based on sum of adjacent cells values and specific data

Re: Conditional formatting based on sum of adjacent cells values and specific data

Re: Conditional formatting based on sum of adjacent cells values and specific data

Re: Conditional formatting based on sum of adjacent cells values and specific data

Re: Conditional formatting based on sum of adjacent cells values and specific data

Re: Conditional formatting based on sum of adjacent cells values and specific data

Re: Conditional formatting based on sum of adjacent cells values and specific data

Re: Conditional formatting based on sum of adjacent cells values and specific data

Thread Information

Users Browsing this Thread

Similar Threads

Conditional Formatting to Check Adjacent Values in Cells

Conditional formatting - highlight multiple cells based on matching values in adjacent row

Conditional formatting based on top 10 values in adjacent cells?

[SOLVED] Conditional Formatting Based on Adjacent Cells and Values

macro for conditional formatting based on the comparing values from adjacent columns

Conditional formatting based on adjacent cell values

[SOLVED] Conditional formatting of adjacent cells based on text in cell

Bookmarks

Bookmarks

Posting Permissions