How to use Excel Count Functions

Excel Count Functions

Overview of Excel Functions

Excel count functions or very handy and help you in a great way to deal with their assets when it comes to Counting stuff inside Excel. In this article, we are going to talk about five different count functions of Excel. 

Overview of Excel Functions

Types of Count Functions in Excel

  • Excel COUNT
  • Excel COUNTA
  • Excel COUNTBLANK
  • Excel COUNTIF
  • Excel COUNTIFS

How to use Excel Count Function

Excel count function comes in very handy when you want to count numbers from a given range. Please note that this function will count only the numbers inside any given range. If there is any cell that contains non-numeric values such as text or number is not formatted as numbers that won’t be counted.

How to use Excel Count Function

In this example, we are trying to count the numbers of A3 to F12 cells. To begin calculation, our function may look something like this:

=Count(A3:F12)

Once you press enter after entering this function, you might notice that it brings you of value 38 that is the number of numbers available in that range. It completely ignores the text values and blank cells. In case you desire to count all the cells that are not blank in that range, we may have to use a different count function in Excel. Let us discuss that one in the next section.

How to use Excel COUNTA Function

In case you desire to count everything in a given range, excluding the blank cells irrespective of the type of value the cell contains Excel CountA function will come in very handy. Do not worry about how to use the Excel COUNTA function. We are here to help you understand how to use the Excel COUNTA function. To perform the calculation on the data set we have in this example, we will use COUNTA as follows:

=COUNTA(A3:F12)

How to use Excel COUNTA Function

The COUNTA function will count everything in that range, excluding the cells which are left blank, returning the value as 58. What if we desire to count the empty cells? We will need to use Excel COUNTBLANK function. Let us discuss the Excel CUONTBLANK function in the next section.

How to use Excel COUNTBLANK Function

The Excel COUNTBLANK function has the capability of counting blank cells in a given range. With the help of Excel COUNTBLANK function, you will be able to find the exact number of empty cells in any given range in your Excel workbooks or worksheets. In our case, the Excel COUNTBLANK function will look something like 

=COUNTBLANK(A3:F12)

How to use Excel COUNTBLANK Function

The function will return us 2 as value as there are only two cells that are blank in the given range. Now let us discuss the other to count functions that are logical in nature in the next sections.

How to use Excel COUNTIF Function

The Excel COUNTIF Function has a unique way of functioning. It takes two arguments to perform the calculations. The first argument is the range, and the second one is the criteria.

  • Range: This is where you want Excel to look for the values to count.
  • Criteria: This is the criteria, if met, the value should be considered for counting.

In our example, we would like Excel to count in the range of results that is in column F and count only where the values are listed as Passed. Here is how we will write the function to perform this calculation.

=COUNTIF(F3:F12, “Passed”)

How to use Excel COUNTIF Function

In this function, we are clearly outlining the range F13 to F12, asking Excel to look only in that specific range to count all the instances where the value is Passed. Imagine you have a situation where you need to set multiple criteria to count. The Excel COUNTIFS function plays a vital role in a situation where we need to consider multiple criteria and ranges. Let us discuss the Excel COUNTIFS function in the next section of this article.

How to use Excel COUNTIFS Function

Excel COUNTIFS Function works very similarly to the Excel COUNTIF Function; however, it takes multiple parameters for arguments to perform the calculation. In our case, we are asking Excel COUNTIFS Function to look for two different criteria in two different ranges. 

We want Excel COUNTIFS Function to look inside the results range and consider the instances where the value is listed as Passed for counting. In the second set of Excel COUNTIFS Function, we are asking the function to scan in the range of the average column and consider any value that is greater than 70 for counting. Only the instances where these two criteria meet i.e., the value you should be Passed, as well as, in the Results column, it should be greater than 70 in the average column to be considered for counting. Our Excel COUNTIFS Function may look something like the following:

=COUNTIFS(F3:F12, “Passed”, E3:E12, “>70”)

How to use Excel COUNTIFS Function

Excel will first look into the range F3 to F12 and consider all instances where the value is is listed as “Passed.” Then it will move to E3 to E12 range and consider any value that is greater than 70. Since we do not have a reference cell to compare the values which are greater than 70, we have provided that under double quotations. This will make the reference check independent and start comparing it with each cell in a given range. The value that will be returned in this case is 3 as there are only three instances where the average is more than 70, and the Result is Passed.

Excel Count functions Video Tutorial

In case you want to watch and learn, here is a quick video demonstration of the Excel count functions.

Click on the following button to download the file used in the demo to practice along with us.

Excel Learning Series – TOC

  1. Excel 2016 – Basics – Find Sum, Average, Min and Max
  2. Excel 2016 Difference Between Formulas and Functions
  3. Excel 2016 Copy Formulas or Functions
  4. Microsoft Excel Auto Fill
  5. Excel 2016 Manage Views using Freeze Panes
  6. Microsoft Excel – Saving Workbooks
  7. Excel Upper and Lower Text Functions
  8. Excel 2016 – Text Function – Proper
  9. Excel Text Functions – Trim Function
  10. Excel – Text Function Left Function
  11. Excel Text Function – Right Function
  12. Excel Text Functions – MID Function
  13. Excel Concatenate Text Function
  14. Excel Nested Functions
  15. Excel Search Function
  16. Excel – Calculate Age using Date Function Today
  17. Calculate Tenure in Excel with Days Function
  18. Autofill Dates in Excel
  19. Formatting Dates in Excel
  20. Excel Date Format Shortcut
  21. Calculating Sum of Time in Excel
  22. Calculate Expiry Date in Excel 2016 – EDATE
  23. Find Age or Tenure using DATEDIF in Excel 2016
  24. RandBetween – Generate Random Dates in Excel 2016
  25. Excel Weekday Function
  26. Excel WeekNum and ISOWeekNum Functions
  27. Excel Networkdays and Excel Networkdays.INTL
  28. Excel Workday and Workdays INTL Functions
  29. Excel DATEVALUE AND TIMEVALUE
  30. Calculate Time Difference in Excel
  31. Calculate Quarter in Excel
  32. Rounding Day, Hour and Minutes in Excel
  33. Excel Pay Rate and Downtime Cost Calculation
  34. Excel Logical Functions IF, AND, OR
  35. Calculate Bonus in Excel Using IF Function
  36. Excel Learning – SumIF and SumIFS
  37. How to use Excel Count Functions
  38. How to use VLOOKUP in Excel
  39. How to vLookup in a different workbook in Excel

We hope you find this article/Demonstration useful. In case you are stuck and or need help, feel free to drop a comment below or submit your question. Do share it further and add your feedback about the article in the comments section.

About Syed Hussaini

I am a business consultant with 15+ Years of extensive expertise in IT Services Management (ITSM), Team Management, Training, Knowledge Management, and Business Operations. My involvement in a corporate environment and freelance projects, my career has taught me how to be more productive, proactive, and independent.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.