Highlight duplicate rows if the summation of values of duplicate rows equals other column value

[Prativa]

Hello Team,

I am having a scenario where I need to find duplicate rows based on three columns let's say in my case it would be Col A,Col B and SL QTY
Once I found duplicate rows my next action would be the sum of values of "QTY" column let's say in my case if it would C2+C3 since row 2 and row 3 are duplicates, next I have to compare the value of C2+C3 = E2.
If C2+C3 equals E2-->highlight the duplicate rows with green color
If C2+C3 > E2-->highlight the duplicate rows with yellow color
If C2+C3 <E2-->highlight the duplicate rows with orange color.

Kindly help me out on how to put this logic in conditional formatting dynamically since I will be having huge records in excel.

Thanks,
Prativa

 

[etaf]

setup 3 conditional format rules


green
=SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)=$E2

yellow
=SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)>$E2

orange
=SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)<$E2

this assumes column E for every duplicate will be the same value

it will also highlight single rows

columns G,H,I are not needed but show the formula working for each condition

Book1
	A	B	C	D	E	F	G	H	I
1	A	B	QTY	AMNT	SL		Green	Yellow	Orange
2	100001	ABCD-123-W	500	964.1	1000		TRUE	FALSE	FALSE
3	100001	ABCD-123-W	500	964.1	1000		TRUE	FALSE	FALSE
4	100001	ABCD-579-W	4	964.1	1		FALSE	TRUE	FALSE
5	100001	ABCD-579-W	2	964.1	1		FALSE	TRUE	FALSE
6	100001	ABCD-579-W	1	964.1	1		FALSE	TRUE	FALSE
7	100001	ABCD-123-A	500	964.1	1100		FALSE	FALSE	TRUE
8	100001	ABCD-123-A	500	964.1	1100		FALSE	FALSE	TRUE
9	100001	ABCD-123-B	250	964.1	750		TRUE	FALSE	FALSE
10	100001	ABCD-123-B	250	964.1	750		TRUE	FALSE	FALSE
11	100001	ABCD-123-B	250	964.1	750		TRUE	FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
G2:G11	G2	=SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)=$E2
H2:H11	H2	=SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)>$E2
I2:I11	I2	=SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)<$E2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E11	Expression	=SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)<$E2	text	NO
A2:E11	Expression	=SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)>$E2	text	NO
A2:E11	Expression	=SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)=$E2	text	NO

 

[etaf]

if you do NOT want to highlight any single entries then use
these 3 rules instead

green
=AND(COUNTIFS($A$2:$A$11,$A2,$B$2:$B$11,$B2)>1,SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)=$E2)

yellow
=AND(COUNTIFS($A$2:$A$11,$A2,$B$2:$B$11,$B2)>1,SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)>$E2)

orange
=AND(COUNTIFS($A$2:$A$11,$A2,$B$2:$B$11,$B2)>1,SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)<$E2)

again columns G,H,I are to illustrate the formula

Book1
	A	B	C	D	E	F	G	H	I
1	A	B	QTY	AMNT	SL		Green	Yellow	Orange
2	100001	ABCD-123-W	500	964.1	1000		TRUE	FALSE	FALSE
3	100001	ABCD-123-W	500	964.1	1000		TRUE	FALSE	FALSE
4	100001	ABCD-579-W	4	964.1	1		FALSE	TRUE	FALSE
5	100001	ABCD-579-W	2	964.1	1		FALSE	TRUE	FALSE
6	100001	ABCD-579-W	1	964.1	1		FALSE	TRUE	FALSE
7	100001	ABCD-123-A	500	964.1	1100		FALSE	FALSE	TRUE
8	100001	ABCD-123-A	500	964.1	1100		FALSE	FALSE	TRUE
9	100001	123565	250	964.1	750		FALSE	FALSE	FALSE
10	100001	ABCD-123-B	250	964.1	750		FALSE	FALSE	TRUE
11	100001	ABCD-123-B	250	964.1	750		FALSE	FALSE	TRUE
Sheet1

Cell Formulas
Range	Formula
G2:G11	G2	=AND(COUNTIFS($A$2:$A$11,$A2,$B$2:$B$11,$B2)>1,SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)=$E2)
H2:H11	H2	=AND(COUNTIFS($A$2:$A$11,$A2,$B$2:$B$11,$B2)>1,SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)>$E2)
I2:I11	I2	=AND(COUNTIFS($A$2:$A$11,$A2,$B$2:$B$11,$B2)>1,SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)<$E2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E11	Expression	=AND(COUNTIFS($A$2:$A$11,$A2,$B$2:$B$11,$B2)>1,SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)<$E2)	text	NO
A2:E11	Expression	=AND(COUNTIFS($A$2:$A$11,$A2,$B$2:$B$11,$B2)>1,SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)>$E2)	text	NO
A2:E11	Expression	=AND(COUNTIFS($A$2:$A$11,$A2,$B$2:$B$11,$B2)>1,SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)=$E2)	text	NO

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

Highlight applicable range >>
A2:E100 - 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:
=AND(COUNTIFS($A$2:$A$11,$A2,$B$2:$B$11,$B2)>1,SUMIFS($C$2:$C$11,$A$2:$A$11,$A2,$B$2:$B$11,$B2)=$E2)
Note the range should be changed to match your data or exceed - my need an additional part of the AND() $A2<>""
Format [Number, Font, Border, Fill] format green
choose the format you would like to apply when the condition is true
OK >> OK

repeat for other colours