### Overview

In this blog post you will learn how to:

• Create a countifs function
• Create a conditional formatting formula and reference cells outside current sheet
• Highlight common records in two lists

Sheet: List 1

Sheet: List 2

### Create named ranges excel 2007

When constructing a conditional formatting formula you can only reference cells on current sheet. But there is a workaround. The answer is named ranges.

Here is how to create named ranges for this example:

1. Click tab "Formulas" on the ribbon
2. Click "Named ranges" button
3. Click "New..." button
4. Type Year in Name field
5. Select range \$A\$2:\$A\$13 in sheet List 2
6. Click OK button
7. Click Close button

Repeat process with remaining ranges.

\$A\$2:\$A\$13 - Year
\$B\$2:\$B\$13 - Asset
\$B\$2:\$B\$13 - Cost

### Setup COUNTIFS function Excel 2007

The countifs function looks for duplicate records in the second list (List 2).

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)
Counts the number of cells specified by a given set of conditions or criteria

In this example:
List 1:Column A - List 2: Column A (Year)
List 1:Column B - List 2: Column B (Asset)
List 1:Column C - List 2: Column C (Cost)

COUNTIFS(YEAR, \$A2, ASSET, \$B2, COST, \$C2)

This formula contains absolute and relative cell references. The conditional formatting formula must look for values in matching columns.

\$A2 is a cell reference to values in column A. The column (\$A) is absolute but the row (2) is relative. When the conditional formatting formula execute the next row, only the row number changes, example \$A3.

### Highlight common records from two lists Excel 2007

How to apply conditional formatting formula:

1. Select cells A2:C11 (Sheet: List 1)
2. Click "Home" tab
3. Click "Conditional Formatting" button
4. Click "New Rule.."
5. Click "Use a formula to determine which cells to format"
6. Type =COUNTIFS(YEAR, \$A2, ASSET, \$B2, COST, \$C2) in "Format values where this formula is TRUE" window.
7. Click "Format.." button
8. Click "Fill" tab
9. Select a color for highlighting cells.
10. Click "Ok"
11. Click "Ok"
12. Click "Ok"

### Highlight common records from two lists Excel 2003

Conditional formatting formula:

=SUMPRODUCT(COUNTIF(\$A2, Year)*COUNTIF(\$B2, Asset)*COUNTIF(\$C2, Cost))

### Functions used in article:

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)
Counts the number of cells specified by a given set of conditions or criteria

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition