Cell equals one of many things

Excel allows a user to check if a cell is equal to one or many things from a range, using the SUMPRODUCT function. As a result, we get True or False. This step by step tutorial will assist all levels of Excel users in checking if a cell is equal to one or many values from another range.

Figure 1. The result of the SUMPRODUCT function

Syntax of the SUMPRODUCT Formula

The generic formula for the SUMPRODUCT function is:

=SUMPRODUCT(--(cell = array))

The parameters of the SUMPRODUCT function are:

• cell – a cell which we want to find in an array
• array – an array where we look up for a cell.

The function compares cell with cells from an array one by one and returns Booleans TRUE or FALSE. Using the double negation “–” in front of the logical test, the Booleans are converted to 1 or 0. In the end, the function sums everything and returns a result.

Setting up Our Data for the SUMPRODUCT Function

Figure 2. Data that we will use in the SUMPRODUCT example

In columns B and C we have two lists of cities which we want to compare. In the cell F2, we want to return the total number of matches between two columns.

Using the SUMPRODUCT to Count Matches Between Two Columns

In our example, we want to check if cells from column A (“City column 1”) is equal to any city in column C (“City column 2”). If it is, the value in column B will be True, otherwise, it will be False.

The formula looks like:

=SUMPRODUCT(--(B3 = $D$3:$D$8)) > 0

The cell is B3 and array is $D$3:$D$8. We need to fix the array, as we are always looking in this array.

To apply the SUMPRODUCT function, we need to follow these steps:

• Select cell C3 and click on it
• Insert the formula: =SUMPRODUCT(--(B3 = $D$3:$D$8)) > 0
• Press enter
• Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

Let’s evaluate the formula and see the result:

Figure 3. Evaluate the SUMPRODUCT function

As you can see, the function has TRUE if the cell B3 is found in the array D3:D8 or FALSE if not. The first value from D3:D8 array is equal to B3, so we have TRUE for this value. These values are converted to 1 and 0 and summed. The sum is 1 which is greater than 0, so the result in C3 is True.

Figure 4. Using the SUMPRODUCT to check if the cell equals one of many things

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.