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
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
Roy Cox
I can add a VBA Function to add by a specific colour, Excel does not have that function built in
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ROY, when I place the formula <=ColourFunction($O$2,K9: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)+
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
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)+
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
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.
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.
ASKER
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
Is it possible to sum the purple (K13 -15) rows and put the sum of those in H24?
FCS-TRAINING_V2.xlsm
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.
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.
ASKER
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.
It doesn't necessarily need a change, merely an adoption of consistency.
ASKER
Thank you both for your assistance.
Pleased to help