How do you calculate a conditional sum for conditional formatting?

M

mkrass

New Member
Joined
Mar 12, 2024
Messages
15
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
I have a spread that is used for labor tracking. Assume every cell represents a day. I want to be able to specify a percentage of labor completed against the plan, every month. So, if there were 80 hours available and someone worked 8 hours a day. The planned hours would be consumed after 10 days. I want to use conditional formatting to change the color of the appropriate cells based on a percentage, i.e., if the percentage is 80%, then every every cell >= 64 hours, i.e. ,days 8, 9, and 10 should have the conditional formatting rule apply. I don't know how to write a formula that will calculate the sum of cell1 against the value of cell 1, then the sum of (cell1 + cell2) against the value of cell 2, the sum of (cell1 + cell2 + cell3) against the value of cell3. In the image that I have uploaded, I would like to set his formula for the range BS17 to CW17. I apologize that I cannot upload a snippet using the XLBB add-in at the moment. I am getting an error message that it cannot run/is not allowed in protected mode.
 

Attachments

  • Conditional - Threshold completion.PNG
    Conditional - Threshold completion.PNG
    136.3 KB · Views: 14

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
in lieu of xl2bb you can post a table. just copy a range of cells and paste into the post. Your image is hard to read and has a lot of data. Just a few rows. And please show expected results.

in your scenario, why is day 8 supposed to be highlighted? 8x8 =64, so that has been met.
 
Upvote 0
I agree with @StephenCrump that this looks very similar to the thread he linked to. But in relation to this ..
mkrass said:
I cannot upload a snippet using the XLBB add-in at the moment. I am getting an error message that it cannot run/is not allowed in protected mode.
Click to expand...
.. look through the suggestions in this thread:
www.mrexcel.com

XL2BB Icons greyed out

Hi Team, i am unable to copy the data throught Xl2bb as buttom got disable please see the screen shot can anyone hlep me this. Regards Sanjeev
www.mrexcel.com www.mrexcel.com
 
Upvote 0
StephenCrump said:
In addition to @awoohaw's questions, perhaps you could also explain, is this question any different (apart from layout) to the similar question solved here:

Highlight cell when a percentage amount has been met.
Click to expand...
Thank you all for your responses. I have been able to get XL2BB installed, so hopefully, the attached sample will help. :)
In response to the question what is different from my post from last week, I would like to understand the syntax change if I need to use/calculate different time blocks, and exclude a summary cell. Also, with the attached sample, the previous solution, that did work, isn't working, and I would like to understand what I did wrong. So, from the included sample. If 0.81 is the percentage, I would expect that cells M3 and cells O3:X3 would be highlighted. The conditional calculation is if the sum is greater than or equal to the sum of the total labor hours and the percentage.
Percentage test.xlsx
BCDEFGHIJKLMNOPQRSTUVWXY
1Time Block 1Block 1 Total Time Block 2Block 2 Total
2Budgeted Hours1/1/241/2/241/3/241/4/241/5/241/6/241/7/241/8/241/9/241/10/241/1/120241/12/241/33/20241/14/241/15/241/16/241/17/241/18/241/19/241/20/24
3160888888888880888888888880
4
50.85
Sheet1
Cell Formulas
RangeFormula
N3,Y3N3=SUM(D3:M3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
X3Expression=SUM($D3:D3) >= $B3*$B5textNO
W3Expression=SUM($D3:D3) >= $B3*$B5textNO
U3Expression=SUM($D3:D3) >= $B3*$B5textNO
T3Expression=SUM($D3:D3) >= $B3*$B5textNO
S3Expression=SUM($D3:D3) >= $B3*$B5textNO
R3Expression=SUM($D3:D3) >= $B3*$B5textNO
Q3Expression=SUM($D3:D3) >= $B3*$B5textNO
P3Expression=SUM($D3:D3) >= $B3*$B5textNO
O3Expression=SUM($D3:D3) >= $B3*$B5textNO
U5:W5Cell Value>="($B$3*$B$5)"textNO
V3Expression=SUM($D3:D3) >= $B3*$B5textNO
M3Expression=SUM($D3:D3) >= $B3*$B5textNO
L3Expression=SUM($D3:D3) >= $B3*$B5textNO
J3Expression=SUM($D3:D3) >= $B3*$B5textNO
I3Expression=SUM($D3:D3) >= $B3*$B5textNO
H3Expression=SUM($D3:D3) >= $B3*$B5textNO
G3Expression=SUM($D3:D3) >= $B3*$B5textNO
F3Expression=SUM($D3:D3) >= $B3*$B5textNO
E3Expression=SUM($D3:D3) >= $B3*$B5textNO
D3Expression=SUM($D3:D3) >= $B3*$B5textNO
J5:L5Cell Value>="($B$3*$B$5)"textNO
K3Expression=SUM($D3:D3)>=$B$3*$B$5textNO
 
Upvote 0
mkrass said:
I have been able to get XL2BB installed ...
Click to expand...
Excellent!

How about ...

ABCDEFGHIJKLMNOPQRSTUVWXY
1Time Block 1Block 1Time Block 2Block 2
2Budgeted Hours1 Jan 20242 Jan 20243 Jan 20244 Jan 20245 Jan 20246 Jan 20247 Jan 20248 Jan 20249 Jan 202410 Jan 2024Total11 Jan 202412 Jan 202413 Jan 202414 Jan 202415 Jan 202416 Jan 202417 Jan 202418 Jan 202419 Jan 202420 Jan 2024Total
3160888888888880888888888880
41010101010101010101010010101010101010101010100
50.6999999999990999999999990
Sheet1
Cell Formulas
RangeFormula
N3:N5,Y3:Y5N3=SUM(D3:M3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:Y5Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
 
Upvote 0
StephenCrump said:
In addition to @awoohaw's questions, perhaps you could also explain, is this question any different (apart from layout) to the similar question solved here:

Highlight cell when a percentage amount has been met.
Click to expand...

StephenCrump said:
Excellent!

How about ...

ABCDEFGHIJKLMNOPQRSTUVWXY
1Time Block 1Block 1Time Block 2Block 2
2Budgeted Hours1 Jan 20242 Jan 20243 Jan 20244 Jan 20245 Jan 20246 Jan 20247 Jan 20248 Jan 20249 Jan 202410 Jan 2024Total11 Jan 202412 Jan 202413 Jan 202414 Jan 202415 Jan 202416 Jan 202417 Jan 202418 Jan 202419 Jan 202420 Jan 2024Total
3160888888888880888888888880
41010101010101010101010010101010101010101010100
50.6999999999990999999999990
Sheet1
Cell Formulas
RangeFormula
N3:N5,Y3:Y5N3=SUM(D3:M3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:Y5Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
Click to expand...
Stephen, Thank you. I applied the formula to teh same sample spreadsheet I uploaded, and no conditional formattong was applied, see enclosed

Percentage test.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1Time Block 1Block 1 Total Time Block 2Block 2 Total
2Budgeted Hours1/1/241/2/241/3/241/4/241/5/241/6/241/7/241/8/241/9/241/10/241/1/120241/12/241/33/20241/14/241/15/241/16/241/17/241/18/241/19/241/20/24
3160888888888880888888888880
4
50.40
Sheet1
Cell Formulas
RangeFormula
N3,Y3N3=SUM(D3:M3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
X3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
W3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
U3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
T3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
S3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
R3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
Q3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
P3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
O3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
U5:W5Cell Value>="($B$3*$B$5)"textNO
V3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
M3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
L3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
J3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
I3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
H3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
G3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
F3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
E3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
D3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
J5:L5Cell Value>="($B$3*$B$5)"textNO
K3Expression=SUMPRODUCT($D3:D3,--ISNUMBER($D$2:D$2))>=$B$3*$B$5textNO
 
Upvote 0
mkrass said:
I applied the formula to teh same sample spreadsheet I uploaded
Click to expand...
  1. Not correctly. You have applied the CF formula individually to lots of separate cells. You need to select thee whole row of cells and apply the CF formula once. However, I don't think that formula will quite do what you want given this from your earlier example.
    mkrass said:
    I would expect that cells M3 and cells O3:X3 would be highlighted.
    Click to expand...
  2. Also, your sample sheet has dates that are not actually dates in some of the row 2 cells (eg O2 and Q2) so that will mess things up too.

This is my suggestion. Delete any existing CF in row 3 then select from D3:Y3 (or further to the right if there are more dates) and then apply this CF

mkrass.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXY
1Time Block 1Block 1 Total Time Block 2Block 2 Total
2Budgeted Hours1/1/20242/1/20243/1/20244/1/20245/1/20246/1/20247/1/20248/1/20249/1/202410/1/202411/1/202412/1/202413/1/202414/1/202415/1/202416/1/202417/1/202418/1/202419/1/202420/1/2024
3160888888888880888888888880
4
50.4
CF (3)
Cell Formulas
RangeFormula
N3,Y3N3=SUM(D3:M3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:Y3Expression=AND(ISNUMBER(D$2),SUMIFS($D3:D3,$D$2:D$2,"<>*Total")>=$B$3*$B$5)textNO
 
Upvote 0
Peter_SSs said:
  1. Not correctly. You have applied the CF formula individually to lots of separate cells. You need to select thee whole row of cells and apply the CF formula once. However, I don't think that formula will quite do what you want given this from your earlier example.

  2. Also, your sample sheet has dates that are not actually dates in some of the row 2 cells (eg O2 and Q2) so that will mess things up too.

This is my suggestion. Delete any existing CF in row 3 then select from D3:Y3 (or further to the right if there are more dates) and then apply this CF

mkrass.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXY
1Time Block 1Block 1 Total Time Block 2Block 2 Total
2Budgeted Hours1/1/20242/1/20243/1/20244/1/20245/1/20246/1/20247/1/20248/1/20249/1/202410/1/202411/1/202412/1/202413/1/202414/1/202415/1/202416/1/202417/1/202418/1/202419/1/202420/1/2024
3160888888888880888888888880
4
50.4
CF (3)
Cell Formulas
RangeFormula
N3,Y3N3=SUM(D3:M3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:Y3Expression=AND(ISNUMBER(D$2),SUMIFS($D3:D3,$D$2:D$2,"<>*Total")>=$B$3*$B$5)textNO
Click to expand...
Peter, Thank you. This is now working. I can see the CF rule being applied.
(I did learn something from your post. I didn't realize you can select a range and apply the CF rule once for all - thank you for that).
But, it appears to include the value of N3 and Y3. Am I reading that correctly? How can the N3 and Y3 column values be excluded from the calculation?
 
Upvote 0
mkrass said:
But, it appears to include the value of N3 and Y3. Am I reading that correctly? How can the N3 and Y3 column values be excluded from the calculation?
Click to expand...
Sorry, I missed the fact that those total columns have merged cells in rows 1 & 2. As it happens that makes the formula marginally simpler.

mkrass.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXY
1Time Block 1Block 1 TotalTime Block 2Block 2 Total
2Budgeted Hours1/1/20242/1/20243/1/20244/1/20245/1/20246/1/20247/1/20248/1/20249/1/202410/1/202411/1/202412/1/202413/1/202414/1/202415/1/202416/1/202417/1/202418/1/202419/1/202420/1/2024
3160888888888880888888888880
4
50.7
CF (3)
Cell Formulas
RangeFormula
N3,Y3N3=SUM(D3:M3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:Y3Expression=AND(ISNUMBER(D$2),SUMIFS($D3:D3,$D$2:D$2,"<>")>=$B$3*$B$5)textNO
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

S
  • Question Question
Help with conditional formatting using a pivot table with dynamic ranges
Replies
1
Views
125
RoryA
RoryA
K
  • Solved
Conditionally sum fractional cells based on column headings
Replies
5
Views
162
Cubist
Cubist
DB73
  • Question Question
calculating percentage based on cell values
Replies
2
Views
141
DB73
DB73
K
  • Solved
Conditionally Format cells based on relationship to a max value
Replies
1
Views
112
Fluff
Fluff
M
  • Question Question
Conditional Formatting/Moving Rows Based on Matching and Opposite Data
Replies
1
Views
278
Madam_Xavier
M

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top