Last week I received the following question from André:
“I need a formula that will count how many values in cells D4:D7 are listed in B4:B13. I don’t want to count the number of times they appear in column B, I just want to know how many values from column D are in column B“.
Here is the data:
The answer is 3. That is values C, D and E are in column B.
Now, we could use a helper column to check if the values in column D are in column B, and then count those that returned a match like this in column H:
There’s nothing wrong with helper columns. In fact they can be more efficient than using array formulas.
However, if you want a formula challenge then go ahead and see if you can write a formula that will return the count in a single cell. Don’t read any further until you’ve had a go yourself.
Excel COUNT MATCH Array Formula
I’m going to step through my approach, however I’m sure it’s not the only way so please share your ideas in the comments below.
Remember: array formulas are entered using CTRL+SHIFT+ENTER and the curly braces are automatically inserted by Excel.
Let’s step through how this COUNT MATCH array formula works. Here it is with colour coding:
The MATCH function looks up the values in cells D4:D7 and returns their position in cells B4:B13. If it doesn’t find a match it returns the #N/A error.
Normally the MATCH function looks up a single value, but because we’ve entered it as an array formula it will look up multiple values, in this case those in cells D4:D7.
When I evaluate the MATCH component of the formula it returns a list (or array) of the positions like so:
That is, C is the 4th item in the range B4:B13, D is the 5th item and E is the 8th item, as you can see below. H isn’t found so MATCH returns an error.
Note: MATCH returns the first instance of a value so the second and third instances of D are ignored. Perfect, that’s what we want.
Finally, the COUNT function simply counts how many numbers the MATCH function returns. Since #N/A is not a number it is ignored.
Download the workbook
Don’t forget to share your ideas for solving this challenge in the comments below.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.