**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:

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**:

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:

1 |
=A2=$E$2 |

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

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**:

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

## 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:

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:

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**:

1 |
=SUMIF(A2:A17,E2,C2:C17) |

**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:

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:

## 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:

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)**.