To highlight SUMIFS rows (the rows that match the SUMIFS criteria) in Google Sheets, we may require two formulas. One is SUMIFS and the other is the REGEXMATCH. The former is optional though.

To adds up a range conditionally we can use the SUMIFS function. To match the criteria in the SUMIFS formula range, I mean to find the rows that match the SUMIFS conditions/criteria, we can use the REGEXMATCH function.

I will explain to you why the SUMIFS is optional in the related conditional formatting later.

As a side note, if you have only one criterion column, then you can use the SUMIF function alone. The REGEXMATCH function is not required in this case – Sumif in Conditional Formatting in Google Sheets.

The Purpose of Highlighting SUMIFS Rows in Google Sheets

In a large dataset, it would be a tough task to manually identify the rows that SUMIFS adds up in total. We may require to go through each and every row to find whether it matches the given condition.

In the below example, the criteria are Name = “A”, Area =”East”, and Month = “Jan”.

The SUMIFS formula in cell H2 (you will get the formula later) won’t give you any clue about the matching rows. It would simply give you a total.

As per the conditions in H1:J1 the SUMIFS total in cell H2 is 13.

I want to highlight the SUMIFS rows if the SUMIFS total is less than the value in cell H3, i.e. <20 (matching rows already highlighted in light yellow color).

Because I want to edit column E in the SUMIFS matching rows to match the SUMIFS total to 20, i.e. the value in H3.

If you highlight the SUMIFS rows you can easily edit the values in column E either manually or using GOAL SEEK.

Note: If you use GOAL SEEK in a row that is not involved in the calculation, you will see the following error.

The goal could not be found. Subsequent iterations were not bringing the Add-on closer to a solution.

The above step (highlighting) helps you use the GOAL SEEK in the correct row (yellow highlighted rows).

In concise, the purpose of highlighting SUMIFS rows in Google Sheets is to easily edit the values in the rows matching the criteria.

How to Highlight SUMIFS Rows Conditionally in Google Sheets

As I have already mentioned, you need two formulas. One is SUMIFS and the other is a REGEXMATCH.

If you simply want to highlight the SUMIFS rows, the REGEXMATCH function is enough.

To add a condition to the highlighting like whether the SUMIFS total is less than, equal to, or greater than a set value (cell H3), we need to use the SUMIFS too.

REGEXMATCH Formula to Conditional Format the SUMIFS Matching Range

Formula Rule:

=regexmatch($B$2:$B$15&$C$2:$C$15&$D$2:$D$15,$H$1&$I$1&$J$1)=true

Just key this formula in the custom formula rule in conditional formatting to highlight the rows matching the SUMIFS criteria (conditions).

I hope, you already know how to insert this formula in the Conditional formatting (Format menu > Conditional formatting). If not, the below screenshot will be useful.

The REGEXMATCH matches the combined values in columns B, C, and D with the combined criteria in cell H1, I1, and J1. A cool trick, right?

Remember this formula just highlights the SUMIFS matching rows. It has no relation to the SUMIFS total.

SUMIFS Formula to Adds Up a Range

=sumifs($E$2:$E,$B$2:$B,$H1,$C$2:$C,$I1,$D$2:$D,$J1)

The above formula returns the total of column E in cell H2 based on the criteria. Similar to the REGEXMATCH formula it also uses the criteria from the cells H1, I1, and J1.

I’m just skipping the formula explanation hoping you may already know the use of this function. If not, you can learn the same from my Google Sheets Functions Guide.

SUMIFS and REGEXMATCH Rule to Highlight SUMIFS Rows in Google Sheets

In order to highlight SUMIFS rows based on its total, we must someway connect the REGEXMATCH and SUMIFS formula. We can use the logical IF to do that.

Our SUMIFS formula is in cell H2. I want to highlight the SUMIFS rows in the range B2:E based on the sum value in cell H2.

I mean if the value in H2 is less than the value in H3, highlight the rows involved in the SUMIFS total.

For that, just modify the earlier REGEXMATCH formula in the conditional format as below. See the IF use at the beginning of the formula.

=if($H$2<$H$3,regexmatch($B$2:$B$15&$C$2:$C$15&$D$2:$D$15,$H$1&$I$1&$J$1)=true)

Change $H$2<$H$3 to $H$2>$H$3 to highlight if the SUMIFS total is greater than the set value in H3.

Additional Resources

• Compare Sumifs, Sumproduct, Dsum with Example in Google Sheets.
• How to Include Same Field Twice in SUMIFS Function in Google Sheets.
• REGEXMATCH in SUMIFS and Multiple Criteria Columns in Google Sheets.
• Difference Between SUMIFS and SUMPRODUCT in Google Sheets.
• Difference Between SUMIFS and DSUM in Google Sheets.
• How to Use Different Criterion in SUMIFS Google Sheets Function.
• How to Include a Date Range in SUMIFS in Google Sheets.
• Sumifs Array Formula Expanding Issue and Alternative Formulas.
• How to Sumif/Sumifs Excluding Duplicates in Google Sheets.
• SUMIFS with OR Condition in Google Sheets.