Conditional Formatting & Formulas

Marcia Morris
Marcia Morris used Ask the Experts™
on
I want to add a conditional formatting to the cells in column G ( 7 - 22) . I want the cells to highlight a certain color blue (RGB 183, 222, 232) if the ship date in column H is delinquent based on today's date OR if the ship date carries through to Sunday of that week. For example today is Monday, Sept 10th. The cells in column G should highlight if the ship date is through Sunday, Sept 16th.

Second, is it possible to also to write a formula in cell H31 to automatically sum up all cells that are highlighted in the blue in column G so that I don't have to manually do the sum each time I move lines around?

Third, is it possible to also to write a formula in cell H32 to automatically sum up all cells that are highlighted in the purple (RGB 204, 192, 218) in column K so that I don't have to manually do the sum each time I move lines around?

Fourth, is it possible to write a formula in cell G32 to automatically sum up all cells that are highlighted in the green (RGB 0, 255, 0) in column K so that I don't have to manually do the sum each time I move lines around?
FCS-TRAINING.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
I can add a VBA Function to add by a specific colour, Excel does not have that function built in
Finance Analyst
Commented:
So the delinquent date is anything less than Sunday after today, so:

Conditional Format with following formula, starting in G9
=$H9<=CEILING(TODAY(),7)+1

CEILING will round up a number to a specific factor. Dates are stored as serial numbers and it happens that day 1 (01 Jan 1900) was a Sunday therefore all Saturdays are a factor of 7; round up to a factor of 7 and plus 1 = Sunday.

You can then use the same condition in a SUMIF formula:

=SUMIF($H9:$H30,"<="&CEILING(TODAY(),7)+1,$G9:$G30)

How are you determining the Purple and Green? Can they be conditional format and then SUMIF using same formula?
Roy CoxGroup Finance Manager
Commented:
This contains the UDF, currently set for COUNT in K32. It can be set to SUM like this

=ColourFunction($O$2,K9:K30,TRUE)

Check it out and let me know if it does what you want
FCS-TRAINING.xlsm
Marcia MorrisSr. Project Manager

Author

Commented:
ROY, when I place the formula <=ColourFunction($O$2,K9:K30,TRUE)> in cell H32 it's not summing up the total dollars from column G. I may have not explained that correctly. Not sure if this request is possible. Currently after I've made changes which involve moving lots of lines around in a daily status meeting, I then go back and recalculate the shipment dollars in by re-inputting the sum formula in cells G24, H23, H24 based on the blue cell color in column G and green/purple cell colors in column K.

I did the sum based on the colors so that you can see what I have to do (rows 9-22). I'm trying to see if it could be automated vs. me having do the sum formula for each bucket.

I'VE ADDED MORE DATA TO THE FILE SO THAT YOU CAN SEE HOW IT LOOKS WHEN I'M IN MY MEEETING.

ROB, the <=$H9<=CEILING(TODAY(),7)+1> & <=SUMIF($H9:$H30,"<="&CEILING(TODAY(),7)+1,$G9:$G30)> worked as indicated. I did notice that when I changed the color of the conditional formatting the SUMIF formula didn't recognize the change. I would expect it to not sum because now the cells are no longer blue. This may be asking too much of this feature but is it possible for it only to change the cells to blue if the color in column K is either green or purple?

The way we determine the purple (testing will be completed this week, which this week means thru Sunday) & green (testing is done or will be completed that day). Then if it's 'shippable' I put a Y in column I and there is a formula that looks at our backlog from the customer POs and if the backlog says for a specific date lets say row 27 (26-Jul-18) that only 2 pieces can ship even though this spreadsheet says 4 pieces are completed then there is a calculation in another cells that shows us visibility of gross vs. actual backlog (we run parts in lot sizes).

I'VE ADDED MORE DATA TO THE FILE SO THAT YOU CAN SEE HOW IT LOOKS WHEN I'M IN MY MEEETING.
FCS-TRAINING_V2.xlsm
Rob HensonFinance Analyst

Commented:
Not sure what you mean in your first paragraph.

Conditional formatting applies the colour that is set in the CF rule based on a condition.

SUMIF adds up based on a condition. It does not look at the colour, it looks at the condition.

If you use the same condition in both, the result of the Sum cannot be different to the sum of the cells formatted in the colour of the CF rule.

If however calculation is set to manual you may have to force calculation by pressing F9

As always it is best to ask the complete question.

By sounds of things the purple and green can be set with more conditions, a test date comparison against this week/today and a Y in column I and the backlog test.
Marcia MorrisSr. Project Manager

Author

Commented:
Hello Rob, thanks for clarifying for me.  I understand now. Can the test date comparison be set with a date given that we also have text in the field with the date?

Is it possible to sum the purple (K13 -15) rows and put the sum of those in H24?
FCS-TRAINING_V2.xlsm
Rob HensonFinance Analyst

Commented:
Can the test date comparison be set with a date given that we also have text in the field with the date?

Yes - if it always consistent format eg "dd/mm/yyyy - text string" in other words date is always in same position and in true date format.
Marcia MorrisSr. Project Manager

Author

Commented:
Rob, thank you. I definitely know that I won't be able to get the team to change the way they input the date because they are not that open to change here.
Rob HensonFinance Analyst

Commented:
It doesn't necessarily need a change, merely an adoption of consistency.
Marcia MorrisSr. Project Manager

Author

Commented:
Thank you both for your assistance.
Roy CoxGroup Finance Manager

Commented:
Pleased to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial