Want to highlight rows if the SUMIF total is less than or greater than a certain value (target) in Google Sheets? Yes, it’s possible! You can use SUMIF in conditional formatting to highlight cells or rows based on whether they meet a specific threshold.

Let’s take an example: Suppose we have the total attendance of two students from 01-01-2019 to 30-06-2019. We want to check whether they meet the minimum required attendance to qualify for an exam.

Condition: A student must have at least 120 days of attendance to be eligible.

See the sample data and how we use SUMIF in conditional formatting to achieve this.

Must Read: Google Sheets Functions Guide (Quickly Learn All Popular Functions in Sheets)

How to Use the SUMIF Formula in Conditional Formatting

Here’s the mock data used in Google Sheets:

Note: In this example, the data is sorted by student name. However, the SUMIF formula works perfectly even if your data is not sorted. Sorting is not a requirement for this conditional formatting to work correctly.

We’ll use a custom SUMIF formula in conditional formatting for two different purposes:

• Highlight cells/rows when the SUMIF total meets the target
• Highlight cells/rows when the SUMIF total doesn’t meet the target

To switch between these, you just need to adjust the comparison operator in the formula.

SUMIF to Highlight Rows if Total is Less Than the Target

SUMIF Custom Formula Rule #1:

=AND($A2<>"", SUMIF($A$2:$A, $A2, $C$2:$C) < 120)

This SUMIF conditional formatting formula highlights rows where the total attendance per student is less than 120. For example, it highlights the rows for “Student 2” in column A.

Note: The AND function ensures that blank rows are excluded from highlighting.

Steps to Apply:

1. Select the range (e.g., A2:C based on your dataset)
2. Go to the Format menu → Conditional formatting
3. Under Format rules, choose Custom formula is
4. Enter the formula above
5. Choose your formatting style
6. Click Done

SUMIF to Highlight Rows if Total is Greater Than or Equal to the Target

Sometimes, you may want to highlight students who meet or exceed the attendance requirement.

SUMIF Custom Formula Rule #2:

=AND($A2<>"", SUMIF($A$2:$A, $A2, $C$2:$C) > 119)

This highlights the rows for “Student 1” because the total attendance is sufficient.

Highlight Only a Specific Column Instead of Entire Rows

Want to highlight just the names in column A instead of full rows? You don’t need to change the formula — just modify the Apply to range.

Use the same formula:

=AND($A2<>"", SUMIF($A$2:$A, $A2, $C$2:$C) > 119)

Then, in the conditional formatting pane, set the Apply to range to A2:A instead of A2:C.

This way, only the names in column A will be highlighted.

As you can see, it’s easy to use SUMIF in conditional formatting without needing any helper columns.

Related Use Cases

Looking for more advanced conditional formatting use cases with SUMIF or SUMIFS?

• Highlight Groups When Their Total Exceeds a Target in Google Sheets — for setting individual target totals per group or category, including support for a second lookup table.
• Highlight SUMIFS Rows Based on Its Total in Google Sheets — to highlight specific rows that match multiple conditions and check whether their total meets a given threshold.

These tutorials expand on this method with more advanced logic for grouped data or multi-criteria highlighting.

This example shows how you can combine aggregation functions with conditional formatting to create powerful, criteria-based highlights in Google Sheets. For a broader collection of techniques—from basic rules to advanced formula-driven scenarios—see: The Ultimate Guide to Conditional Formatting in Google Sheets.