conditional formatting based on the sum of values which are based on another column values

khostomsky

New Member
Joined
Mar 19, 2022
Messages
4
Office Version
  1. 2021
Platform
  1. Windows
Hello,
I need a (maybe not so) special conditional formatting. The situation is this.
In column "A" I have unique IDs (lets assume names), which can repeat in rows (this is important) but not necessarily. To each unique value belongs a value in a different column "B". I need to highlight a situation when to a specific unique ID in column "A" the SUM of the values in column "B" reach a certain value.

Example:
If to a certain ID the SUM of the values in column "B" reach "10" then should be highlighted the row (or cell).

1647688792846.png


I feel that it is probably not so complicated hence I am unable to formulate it in a working way.

Thank you!
Karel
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
select the area A & b you want to format
if starting at row 2
=SUMIF( $A$2:$A$100, $A2, $B$2:$B$100) >= 10

Book1
AB
1IDValue
2Peter8
3John1
4Harry4
5Sally4
6Jill2
7Peter2
8John1
9Harry0
10Sally8
11Jill1
12Peter1
13John1
14Harry4
15Sally12
16Jill9
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:B16Expression=SUMIF($A$2:$A$100,$A2,$B$2:$B$100)>=10textNO


for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A2:B100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=SUMIF( $A$2:$A$100, $A2, $B$2:$B$100) >= 10

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK
 
Upvote 0
select the area A & b you want to format
if starting at row 2
=SUMIF( $A$2:$A$100, $A2, $B$2:$B$100) >= 10

Book1
AB
1IDValue
2Peter8
3John1
4Harry4
5Sally4
6Jill2
7Peter2
8John1
9Harry0
10Sally8
11Jill1
12Peter1
13John1
14Harry4
15Sally12
16Jill9
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:B16Expression=SUMIF($A$2:$A$100,$A2,$B$2:$B$100)>=10textNO


for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A2:B100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=SUMIF( $A$2:$A$100, $A2, $B$2:$B$100) >= 10

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Hello etaf,
thank you, but could you check what is the problem?

Thanks,
K.

1647690176490.png
 
Upvote 0
i suspect the space between > and =

Book1
ABC
1IDValue12
2Peter8
3John1
4Harry4
5Sally4
6Jill2
7Peter2
8John1
9Harry0
10Sally8
11Jill1
12Peter1
13John1
14Harry4
15Sally12
16Jill9
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:B16Expression=SUMIF($A$2:$A$100,$A2,$B$2:$B$100)>=$C$1textNO
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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