Count Exact Matches: Excel Question
Here’s an Excel question from today’s mailbag. MK asks:
- I am trying to use countif to find the number of times a word exactly appears in a column – e.g. count the no of times “PACK” appeared in a column excluding “pack” etc. I remember seeing a combination of countif and exact functions used in conjunction somewhere, but not able to figure out the right formula.
To solve this problem, MK could use the SUMPRODUCT and EXACT functions in a formula. As shown in Example 6, this technique will count items that are an exact match, including the upper and lower case.
For example, to match the contents of cell C2, in a list in column A, use this formula:
=SUMPRODUCT(–EXACT($A$2:$A$11,C2))
If you have another solution, please post it in the comments. Thanks!
____________