Explanation
In this example, the goal is to count the number of items sold and remaining, based on the data visible in columns B and C. The ID column holds unique ids, and the Sold column is used to record a sale. An “x” in the Sold column indicates the item has been sold. As is typical in Excel, there are several ways to solve this problem. The article below explains two approaches.
COUNTA function
The COUNTA function counts non-blank cells in a range. Unlike the COUNT function, which only counts numeric values, COUNTA will count any value in a cell, including numbers and text. The first formula in F5 counts the total items available:
=COUNTA(B5:B16) // returns 12
The result is 12 since there are 12 values in the ID column. The second formula counts the number of items that have been sold:
=COUNTA(C5:C16) // returns 7
The result is 7 since there are 7 values in the Sold column. Note that COUNTA doesn’t care what value is in a cell. In the example shown, we are using an “x” to indicate sold items, but COUNTA would count “y” or “z” in the same way. The last formula counts the number of remaining items:
=COUNTA(B5:B16)-COUNTA(C5:C16) // returns 5
The result is 5 since 12 minus 7 equals 5. In this example, last formula above is an all-in-one formula, to provide more detail. However, in this particular case, the best practice would be to write the last formula like this:
=F5-F6 // use existing values
In other words, we simply re-use existing results. This minimizes the number of calculations performed and reduces errors.
COUNTIF function
The COUNTIF function counts values in a range based on supplied criteria . With COUNTIF the problem can be solved a bit differently. To count total items, you can use COUNTIF like this:
=COUNTIF(B5:B16,"<>") // count not blank
To count sold items you can use either of these formulas:
=COUNTIF(C5:C16,"x") // count equal to "x"
=COUNTIF(C5:C16,"<>") // count not blank
To count items not sold, you can use COUNTIF like this:
=COUNTIF(C5:C16,"") // count blank
Match test
=SUMPRODUCT(--(B5:B11=C5:C11))
For more information about how this formula works, see this explanation .
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 .
