Conditional Formatting with SUMIF Function in Excel

Conditional Formatting is one of the best methods for highlighting our data based on certain criteria. We can highlight the cells based on their value, or put a certain condition that the value in cells has to fulfill to be different from the rest of the cells in the range.

In the example below, we will show how can we combine Conditional Formatting and SUMIF Function.

Conditional Formatting Basics

First thing first, we will create the table with sales data that will consist of different people, the month in which the sales were achieved, and the sales that were achieved in a particular month:

Graphical user interface, table

Description automatically generated

For the next thing, we are going to select all the data we have in column A (all the salesperson names) and then go to the Home tab >> Styles >> Conditional Formatting >> New Rule:

Graphical user interface, application, table, Excel

Description automatically generated

Under the new window, we will choose the last option- Use a formula to determine which cells to format, and then define the following formula:

This is what our formula looks like in the Excel file:

Graphical user interface, text, application

Description automatically generated

The reason why we use cell $E$2 is that we will use this cell to insert the names of different salespersons and to check their sales in cell F2. We will choose a proper format, in our case, it will be red background, by clicking Format and then choosing the appropriate color in the Fill tab:

Graphical user interface, application

Description automatically generated

When we click OK two times, and insert the name “Margaret” in cell E2, this is the result we will get in our table:

Table

Description automatically generated

Using SUMIF Function

Now we can use the SUMIF function to combine with the defined approach. Before that, we will define the Conditional Formatting for column C as well, to be dependable on the value in cell E2. We will use the same approach as before, and will insert a formula to determine the cells to format:

Graphical user interface, text, application, email

Description automatically generated

For the cells that are matching these conditions, we will fill them with blue color. We will click OK twice again, to finish this step.

Our table will be changed, and it will look like this:

Table

Description automatically generated

In cell F2, we will now insert the SUMIF formula, which will encompass our table and the value in cell E2. This is the formula we will insert in cell F2:

SUMIF has three parameters: 1) Range (in our case range A2:A17)- the location where our value should be searched for; 2) Criteria (value in cell E2)- value that we need to search in the range; 3) Sum_range (in our case, that will be column C)- the range that we want to add if the conditions are met.

Value in our cell F2 is $626,573, which is exactly the correct amount for all the sales that Margaret achieved in the first four months of the year:

Graphical user interface, application, table, Excel

Description automatically generated

All we need to do to find the total sales for the other salesperson is to change the name in cell E2. When we insert John, for example, total sales and formatting in our table will be adjusted:

Graphical user interface, application, table, Excel

Description automatically generated

Using SUMIFS Function

We can also use the SUMIFS formula to extract more useful data from our table. The structure of the SUMIFS sentence is a bit different than for SUMIF.

SUMIFS formula will be inserted in cell G2, and we will define the following parameters: 1) Range C2:C17 as a sum_range; 2) First criteria range will be column A; 3) Criteria for the first range will be the value in cell E2; 4) Second criteria range will be the sales figures in column C; 5) Criteria for the second range will be “>200000” which means that we only want to show the sales figures over $200,000 for every person on a monthly level.

We will use Rachel’s data (insert her name in cell E2), and this is what we will end up with:

Graphical user interface, application, table, Excel

Description automatically generated

Although we have every figure for Rachel highlighted, the formula in cell G2 will omit the data in row number eight, as the figures in this row are smaller than the desired number ($200,000).

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.