Highlight rows if Sumif total is less than or greater than a certain value (target) in Google Sheets. Is this possible? Yes! You can use Sumif in conditional formatting in Google Sheets for these types of cell/row highlighting.
As an example, consider the total attendance of two students for the period 01-01-2019 to 30-06-2019. I want to check whether they have sufficient attendance to participate in an exam.
Condition: If a student has 119+ attendances, then he/she is eligible to attend the exam. See the sample data and how I am using Sumif in conditional formatting.
Must Read: Google Sheets Functions Guide (Quickly Learn all Popular Functions in Sheets)
How to Use Sumif Formula in Conditional Formatting in Google Sheets
I have the data (mock-up) entered as below in Google Sheets.
In this sample, column A contains the name of students, column B contains the month, and column C contains the total attendance of the students in each month.
Here I am going to give you a custom Sumif formula to use in conditional formatting that serves two different purposes.
- Highlight cells/rows when Sumif total meets the target.
- Highlight cells/rows when Sumif total doesn’t meet the target.
For these tow different purposes, you must change the comparison operator in the formula that I am going to provide.
First, I am going to detail the point # 1. See the formula to highlight the students, whose attendances do not meet the required total attendance.
Sumif to Highlight Rows if Sumif Total is Less Than the Target Value
Sumif Custom Formula Rule # 1:
=and($A2<>"",sumif($A$2:$A,$A2,$C$2:$C)<120)I have already set this Sumif conditional formatting formula that highlights the rows containing “Student 2” in column A as his/her total is less than the required target which is 120.
See how to apply this Sumif formula in conditional formatting in Google Sheets.
Note: I have used the AND logical operator to exclude blank rows in the highlighting.
Steps:
- Go to the “Format” menu and select “Conditional formatting”.
- In the Conditional formatting pane, the “Apply to rage” as per the above sample data is A2:C.
- Under “Format rules” select “Custom formula is” from the drop-down.
- Enter the above Sumif formula in the provided blank field.
If you have any doubt applying the above Sumif formula in Conditional formatting in Google Sheets, here is the screenshot of the editor pane.
Sumif to Conditional Format Rows if Sumif Total is Greater Than the Target Value
Some times you may want to highlight the rows in which values in a specific column met the target conditionally.
I mean to highlight the students who have sufficient attendance to appear in an exam.
If you follow the above data, then, for this new purpose, the Sumif formula for conditional formatting will be as follows.
Sumif Custom Formula Rule # 2:
=and($A2<>"",sumif($A$2:$A,$A2,$C$2:$C)>119)This will highlight the rows 2 to 7 (Student 1) as he/she has sufficient attendance to appear in the exam.
How to Highlight a Particular Column Instead of Multiple Columns in Sheets
I know some of you may only want to highlight the names in column A instead of the rows. With minimal changes in the formula, you can do that.
=and(A2<>"",sumif($A$2:$A,A2,$C$2:$C)>119)Compare this formula with the Sumif Custom Formula Rule # 2. You can find where I have made the changes.
Similar: Highlight an Entire Row in Conditional Formatting in Google Sheets.
As you can see, I have used Sumif in conditional formatting without using any helper column in Google Sheets. Hope you have liked it.
Hi,
I’m trying to change the font color in cells under column M to bold and red if the sum of the cells in the row for each cell equals zero.
I’m using your formula as so:
Range: M4:M56
Custom condition:
=AND($D4="Approved",SUM($P4:$AA4)=0)What am I missing?
Hi, Joe,
Use SUM, not SUMIF. You can try …
=AND($D4="Approved",SUM($P4:$AA4)=0)Thanks, I tried that, but it didn’t work initially.
However, eventually, I figured why.
I have 6 other conditional formatting on the sheet, and this one was the last.
But when I moved to the top of the list, it worked.
I’m not sure why? But all of them seem to work properly now.
Hi, Joe,
The custom highlight rule order does matter in conditional formatting.
Hello, I’m trying to use this formula in the conditional formatting but it doesn’t work:
SUMIFS(Cobros!$D:$D;Cobros!$I:$I;$B2;Cobros!$J:$J;MONTH(U$1);Cobros!$K:$K;YEAR(U$1)-2000)) = U2
The “SUMIFS” works perfectly if I use it in another cell, but it doesn’t work in the conditional formatting. Can you help me, please?
Regards,
Guillermo
Hi, Guille,
Enter your following SUMIFS in cell U3.
=SUMIFS(Cobros!$D:$D,Cobros!$I:$I,$B$2,Cobros!$J:$J,MONTH($U$1),Cobros!$K:$K,YEAR($U$1)-2000)
Then in conditional formatting try these rules (first select entire sheet).
Rule 1:
=isblank($I1)=trueRule 2:
=and($U$2=$U$3,regexmatch(to_text(row($A1:$A)),textjoin("|",1,filter(row($A$1:$A),$I:$I=$B$2,
$J:$J=month($U$1),$K:$K-year($U$1)-2000))))
See if that works?