• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel COUNT MATCH Array Formula

You are here: Home / Excel Formulas / Excel COUNT MATCH Array Formula
November 17, 2016 by Mynda Treacy

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:

count match data sample

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:

helper column to check values

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.

I used the COUNT and MATCH functions in an array formula like so:

count and match functions in an array formula

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:

=COUNT(MATCH(D4:D7,B4:B13,0))

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:

=COUNT({4;5;8;#N/A})

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.

match function 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.

=COUNT({4;5;8;#N/A})
=3

Download the workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Want More?

  • Learn the COUNT Function.
  • Learn the MATCH Function.
  • Learn array formulas.

Don’t forget to share your ideas for solving this challenge in the comments below.

More Excel Formulas Posts

ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel Formulas
Previous Post:create an excel add-in for user defined functions udfsCreate an Excel Add-In for User Defined Functions (UDF’s)
Next Post:Count, Sum and Average Colored Cellscount sum average colored cells

Reader Interactions

Comments

  1. Jim 100X

    November 23, 2016 at 4:29 am

    First, I’d love to know more about the structure of Neale’s SUMPRODUCT and why that worked.

    Next, both solutions seem to be limited by the fact that column D has no duplicates. How would the formulas need to change if column D potentially had duplicate values.

    Reply
    • Mynda Treacy

      November 23, 2016 at 1:32 pm

      Hi Jim,

      In English Neale’s formula

      =SUMPRODUCT((COUNTIF(B4:B13,D4:D7)>=1)*1)

      reads:

      Count the cells in B4:B13 that match the cells in D4:D7, with COUNTIF evaluated the formula now looks like this:

      =SUMPRODUCT(({1;3;1;0}>=1)*1)

      Then test the values returned by COUNTIF to see if they’re greater than or equal to 1. The formula now looks like this:

      =SUMPRODUCT({TRUE;TRUE;TRUE;FALSE}*1)

      Multiplying TRUE and FALSE by 1 converts them to their numeric equivalents of 1 and zero, so the formula now looks like this:

      =SUMPRODUCT({1;1;1;0})

      And SUMPRODUCT adds those values up to return 3.

      In regards to your other question about column D containing duplicates, I don’t see what the purpose of this exercise would be. If you had duplicates in both columns then what result are you trying to return?

      Mynda

      Reply
      • Jim 100X

        November 28, 2016 at 11:11 pm

        Thank you for the explanation of the SUMPRODUCT. That makes a lot more sense now.

        As for my additional question. I’m thinking about what would happen if both lists were rather long. It would be possible to get a duplicate value in the “Match” list and not see it. Is there a way around this problem (the counts aren’t correct if there is a duplicate value in the “Match” list) or would it just be easier to always remove the duplicates from the match list first.

        Reply
        • Mynda Treacy

          November 29, 2016 at 9:00 am

          Hi Jim,

          I’d be inclined to remove duplicates (Data tab > Remove Duplicates) before starting as opposed to trying to complicate the formula to handle them somehow.

          Mynda

          Reply
  2. Dave Bonin

    November 22, 2016 at 5:45 am

    I avoid array formulas like the plague. I stopped using them several years ago and never looked back.

    Why? Several reasons:
    1) You have to use Ctrl-Shift-Enter to get the formula to work as an array formula.
    2) I always forget to use Ctrl-Shift-Enter.
    3) You will, too.
    4) If you forget to use Ctrl-Shirt-Enter, the broken array formula does not tell you it’s broken.
    5) Most normal users have no idea what an array formula is nor how not to break them.
    6) Short of writing a macro (which I did), there’s no easy way to see where array formulas are, thereby making it harder to find out which one(s) you or someone else accidentally broke.

    What do I use instead? The SUMPRODUCT() function! It can do almost anything (maybe everything) an array formula can do and there’s no Ctrl-Shift-Enter to mess around with.

    Reply
    • Mynda Treacy

      November 22, 2016 at 8:46 am

      Great points, Dave.

      SUMPRODUCT is in fact a ‘modern’ array function, in that it handles arrays of data, but it doesn’t require using CTRL+SHIFT+ENTER. For those interested you can learn more about SUMPRODUCT here.

      Mynda

      Reply
  3. Sunny Kow

    November 18, 2016 at 9:42 am

    I will use a helper column with VLOOKUP and COUNTIFS.
    In cell J4 =VLOOKUP(D4,$B$4:$B$13,1,FALSE) copied down till J7.
    In cell J8 =COUNTIFS(J4:J7,”#N/A”)
    I will normally avoid using array formulas.

    Reply
    • Mynda Treacy

      November 18, 2016 at 10:33 am

      Hi Sunny,

      I thought about a similar VLOOKUP solution too.

      I think your COUNTIFS is meant to be:

      =COUNTIFS(J4:J7,"<>"&"#N/A")

      Mynda

      Reply
      • Sunny Kow

        November 18, 2016 at 11:04 am

        It looks like the symbols were auto removed when I posted them.

        Reply
        • Mynda Treacy

          November 18, 2016 at 11:15 am

          Ah, that would explain it.

          Reply
  4. Neale Blackwood

    November 17, 2016 at 11:08 pm

    Non-array solution

    =SUMPRODUCT((COUNTIF(B4:B13,D4:D7)>=1)*1)

    Seems to work OK.

    Reply
    • Mynda Treacy

      November 18, 2016 at 9:38 am

      Nice!

      Reply
  5. Stephen Nicholson

    November 17, 2016 at 11:08 pm

    I always thought that for every array formula there was a SUMPRODUCT equivalent.

    However, in this instance, the closest that I found was:
    =SUMPRODUCT((B4:B13=D4)+(B4:B13=D5)+(B4:B13=D6)+(B4:B13=D7)*1)

    – which produces a result of 5, being number of instances of Match values appearing in the List values.

    In some cases this would be more useful, but it is obviously not the same as returning a count of how many of the Match values appear in the List values.

    Is there a SUMPRODUCT equivalent for this problem?

    Reply
    • Mynda Treacy

      November 18, 2016 at 9:39 am

      Hi Stephen,

      Thanks for having a go. Neale’s SUMPRODUCT version works.

      Cheers,

      Mynda

      Reply
      • Stephen

        November 20, 2016 at 11:50 am

        Excellent!

        I knew that there had to be a SUMPRODUCT solution there somewhere. đŸ™‚

        Reply
  6. André Croteau

    November 17, 2016 at 9:48 pm

    I have replied to you personally this past week, but let me thank you again in this forum for helping me with my query , and quickly too! André

    Reply
    • Mynda Treacy

      November 17, 2016 at 10:24 pm

      đŸ™‚ my pleasure, AndrĂ©

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.