Conditional Formatting & Formulas

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
Marcia MorrisProject ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
I can add a VBA Function to add by a specific colour, Excel does not have that function built in
0
Rob HensonFinance AnalystCommented:
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?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
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
0
Marcia MorrisProject ManagerAuthor 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
0
Rob HensonFinance AnalystCommented:
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.
0
Marcia MorrisProject ManagerAuthor 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
0
Rob HensonFinance AnalystCommented:
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.
0
Marcia MorrisProject ManagerAuthor 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.
0
Rob HensonFinance AnalystCommented:
It doesn't necessarily need a change, merely an adoption of consistency.
0
Marcia MorrisProject ManagerAuthor Commented:
Thank you both for your assistance.
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.