To count total matches in two ranges using Excel, you can use a formula based on the SUM and COUNTIF functions.
How to count total matches in two ranges?
- Select cell G3.
- Type =SUM((COUNTIF(range1, range2)>0)*1), where range1 and range2 are the two ranges you want to compare.
- Press Enter.
- The formula counts the total number of matches between the two ranges.
Example
In this example, we have two datasets labeled as “Range1” and “Range2,” containing lists of fruits. The goal is to write a formula to calculate the total number of matches between the two ranges, where a match is defined as an entry in “Range1” also appearing in “Range2.” The formula used in cell G3 evaluates these matches by checking each value in “Range1” against “Range2” and summing up the occurrences. The result in cell G3 represents the total count of overlapping values between the two ranges.
Formula:
=SUM((COUNTIF(range1, range2)>0)*1)
In this example, it returns 9, indicating there are nine items in range2 that also appear in range1.
Explanation
Evaluate the formula from the inside out!
COUNTIF(range1, range2): The COUNTIF function is used here with two arguments.
- range1: This is the range of cells in the first column (Range1 in the image) that contains items like “Apple,” “Banana,” etc.
- range2: This is the range of cells in the second column (Range2) with items like “Pineapple,” “Kiwi,” etc.
- COUNTIF counts the occurrences of a specific criterion within a range. But here, the COUNTIF(range1, range2) is an array operation, evaluating range2 as an array of criteria. COUNTIF(range1, range2) produces an array where each element represents the count of each item in range2 within range1. For example, if “Apple” appears in both range1 and range2, the resulting array will contain a value greater than 0 for “Apple.”
Logical test: (COUNTIF(range1, range2)>0). This part of the formula applies a logical test to each element in the array generated by COUNTIF(range1, range2).
- The test >0 checks if each count in the array is greater than 0, which indicates that a match exists for that item.
- It returns an array of TRUE (1) and FALSE (0) values. TRUE means a match was found in range1 for a corresponding item in range2, while FALSE means no match was found.
Multiplying (COUNTIF(range1, range2)>0) * 1: Multiplying (COUNTIF(range1, range2)>0) by 1 coerces the TRUE and FALSE values into 1s and 0s, respectively. This transformation is essential because SUM can only add numerical values, not logical values.
SUM((COUNTIF(range1, range2)>0) * 1): The SUM function adds up all the 1s in the array, counting the number of TRUE values. This final result represents the total count of unique matches between range1 and range2.
Workaround for non-Microsoft 365 users
Let’s break down how this formula =SUMPRODUCT(COUNTIF(range1, range2)) works.
Here, COUNTIF is applied in a different way than usual. Instead of a single criteria, range2 is given as an array of criteria. This approach makes COUNTIF return an array of counts, where each element represents the count of a corresponding item in range2 within range1.
For example, if range1 contains three occurrences of “Apple” and “Apple” appears in range2, then the array returned by COUNTIF(range1, range2) will include 3 for that position.
SUMPRODUCT takes the array produced by COUNTIF(range1, range2) and sums all the counts. This means it adds up the total occurrences of each item in range2 found within range1. Unlike the previous formula, this one does not filter for unique matches but instead counts each occurrence. If “Apple” appears multiple times in both ranges, each occurrence is counted.
In this case, the formula returns 21, indicating there are 21 matches when counting each occurrence in range2 that appears in range1. This total includes all repeated matches, which explains why the result is higher than in a unique match count.
Finally, you can download the practice file.