Explanation
In this example, the goal is to count the number of exact matches in two ranges, ignoring the sort order or location of the values in each range. This problem can be solved with the COUNTIF function or with the MATCH function. Each approach is explained below.
Note: Both formulas below use the SUMPRODUCT function to “tally up” the final count, because SUMPRODUCT handles arrays natively in Legacy Excel . In the current version of Excel, which supports dynamic arrays , you can use the SUM function instead.
COUNTIF function
The COUNTIF function counts values in a range that meet supplied criteria. Normally, you would give COUNTIF a range like A1:A10 and criteria like “red”:
=COUNTIF(A1:A10,"red") // count "red" cells
COUNTIF would then return a count of cells in A1:A10 that are equal to “red”. In this case, however, we are giving COUNTIF a range of values for criteria, which causes COUNTIF to return a count for each value. The formula in cell F5 is:
=SUMPRODUCT(COUNTIF(range1,range2))
Working from the inside out, we provide range1 (B5:B16) for range and range2 (D5;D13) for criteria:
COUNTIF(range1,range2)
Because we are giving COUNTIF a range that contains 9 values for criteria , COUNTIF will return 9 counts in an array like this:
{1;1;1;0;0;1;1;1;1}
Each item in this array represents a count. A positive number represents the count of a value in range2 that was found in range1 . A zero indicates a value that was not found. COUNTIF returns this array of counts directly to the SUMPRODUCT function :
=SUMPRODUCT({1;1;1;0;0;1;1;1;1}) // returns 7
With just one array to process, SUMPRODUCT sums the array and returns 7 as a final result.
MATCH function
Another way to solve this problem is with the MATCH function like this:
=SUMPRODUCT(--ISNUMBER(MATCH(range1,range2,0)))
Working from the inside out, the MATCH function is configured with range1 as lookup_value , range2 as lookup_array , and match_type as 0 for an exact match.:
MATCH(range1,range2,0)
Because we are asking MATCH to find 12 values, we get back an array with 12 results like this:
{8;#N/A;#N/A;1;9;6;#N/A;2;#N/A;7;#N/A;3}
In this array, a number represents the position of a matched value, and the #N/A error indicates a value that was not found . To convert this array to TRUE and FALSE values, we use the ISNUMBER function :
--ISNUMBER({8;#N/A;#N/A;1;9;6;#N/A;2;#N/A;7;#N/A;3})
ISNUMBER returns TRUE for any number and FALSE for anything else, so the resulting array looks like this:
{TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}
By default, the SUMPRODUCT function will ignore the logical values TRUE and FALSE, so we use a double negative (–) to convert the TRUE and FALSE values to 1s and 0s. The resulting array is returned to the SUMPRODUCT function:
=SUMPRODUCT({1;0;0;1;1;1;0;1;0;1;0;1}) // returns 7
With only one array to process, SUMPRODUCT sums the array and returns a final result of 7.
MATCH with COUNT
As I mentioned above, in the current version of Excel, which supports dynamic array formulas , you can use the SUM function instead of the SUMPRODUCT function like this:
=SUM(--ISNUMBER(MATCH(range1,range2,0)))
The reason SUMPRODUCT is traditionally used is that it avoids the requirement of entering the formula as an array formula with control + shift + enter in older versions of Excel. However, an interesting result of dynamic arrays in Excel is that they make new solutions possible, because the native array behavior affects older functions as well. For example, in the current version of Excel, we can use the COUNT function directly like this:
=COUNT(MATCH(range1,range2,0))
COUNT is programmed to count only numeric values — it returns the count of numbers in the array returned by MATCH and simply ignores the #N/A errors. The formula evaluates like this:
=COUNT(MATCH(range1,range2,0))
=COUNT({8;#N/A;#N/A;1;9;6;#N/A;2;#N/A;7;#N/A;3})
=7
To be clear, this formula will also work in older versions of Excel. However, it must be entered as an array formula with control + shift + enter. In the current version of Excel , it just works.
Match across rows
The formulas above do not care about the location of values in the two ranges. If you want to compare two ranges and count matches at the row level (i.e. only count matches when the same item appears in the same position), you’ll need a different formula .
Explanation
Traditionally, counting unique items with an Excel formula has been a tricky problem, because there hasn’t been a dedicated unique function. However, that changed when dynamic arrays were added to Excel 365 , along with several new functions, including UNIQUE.
Note: In older versions of Excel, you can count unique items with the COUNTIF function, or the FREQUENCY function, as explained below.
In the example shown, each row in the table represents a stock trade. On some dates, more than one trade is performed. The goal is to count trading days – the number of unique dates on which some kind of trade occurred. The formula in cell G8 is:
=COUNT(UNIQUE(date))
Working from the inside out, the UNIQUE function is used to extract a list of unique dates from the named range date :
UNIQUE(date) // extract unique values
The result is an array with 5 numbers like this:
{44105;44109;44111;44113;44116}
Each number represents an Excel date , without date formatting . The 5 dates are 1-Oct-20, 5-Oct-20, 7-Oct-20, 9-Oct-20, and 12-Oct-20.
This array is delivered directly to the COUNT function :
=COUNT({44105;44109;44111;44113;44116}) // returns 5
which returns a count of numeric values, 5, as the final result.
Note: The COUNT function counts numeric values, while the COUNTA function will count both numeric and text values. Depending on the situation, it may make sense to use one or the other. In this case, because dates are numeric, we use COUNT.
With COUNTIF
In an older version of Excel, you can use the COUNTIF function to count unique dates with a formula like this:
=SUMPRODUCT(1/COUNTIF(date,date))
Working from the inside out, COUNTIF returns an array with a count for every date in the list:
COUNTIF(date,date) // returns {2;2;3;3;3;2;2;2;2;3;3;3}
At this point, we have:
=SUMPRODUCT(1/{2;2;3;3;3;2;2;2;2;3;3;3})
After 1 is divided by this array, we have an array of fractional values:
{0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333}
This array is delivered directly to the SUMPRODUCT function . SUMPRODUCT then sums the items in the array and returns the total, 5.
With FREQUENCY
If you are working with a large set of data, you might have performance problems with the COUNTIF formula above. In that case, you can switch to an array formula based on the FREQUENCY function :
{=SUM(--(FREQUENCY(date,date)>0))}
Note: This is an array formula and must be entered with control + shift + enter, except in Excel 365.
This formula will calculate faster than the COUNTIF version above, but it will only work with numeric values. For more details, see this article .
