How to count items in list in Excel

In this article, we will learn How to count items in list in Excel.

Scenario:

For instance, when working with a long dataset having names, numbers, dates or any other type of dataset. Sometimes we need to count the matching rows meeting some criteria in names, numbers or dates. For this kind of problem we use the COUNTIFS function in Excel to match multiple criteria in lists.

How to solve the problem?

The COUNTIFS function takes any number of arguments, but it must be in pairs. Where the first argument of the pair is criteria range and the second will be criteria itself. Let's see how to use it in a general formula for different lists in a dataset. For a long dataset use column index as criteria range.

Formula Syntax:

= COUNTIFS ( col1:col1 , criteria1, col2:col2, criteria2, ….)

col1 : first column, list where criteria is applied

criteria1 : first criteria applied on first list

col1 : second column, list where criteria is applied

criteria1 : first criteria applied on second list

Example :

All of these might be confusing to understand. Let's take a sample data and run some criterias in the formula to understand it better. Here we have a subcategory list with Sales and Profit values upto 9995 rows.

And we have some criteria where we need to find the count of rows matching criteria in the list. So reference the list using the column Index.

Use the formula :

= COUNTIFS ( A:A , E6 )

A:A : all subcategory names in column list A

E6 : Criteria as cell reference in E6 cell which is "Bookcases"

The formula looks like as shown in the above snapshot. Press Enter to get the count of Bookcases in list A.

There are 228 rows matching Bookcases in column list A. Now apply criteria as Sales numbers greater than 100 and matching Binders in Subcategory list using the formula shown below.

Use the formula :

=COUNTIFS(A:A,E7, B:B,">100")

Here the second criteria on Sales list is given as an argument directly in the formula. This formula returns the number of Binders which have sales greater than 100.

Now apply the formula for different lists and different criteria.

You can repeat criteria lists to apply two or more criteria in the same list. There are 64 Storage items in subcategory where sales is between 100 and 200 and Profit is between 10 and 200.

Use the SUMPRODUCT function to count rows in the list. Learn more about Countif with SUMPRODUCT in Excel here.

Here are some observational notes shown below.

Notes:

The formula works with numbers, text, date values, etc.
Use the same data type to match criteria in criteria list like name in names list, date in date values.
Lists as criteria ranges must be of same length, or the formula returns #VALUE! error.
Operators like equals to ( = ), less than equal to ( <= ), greater than ( > ) or not equals to ( <> ) can be used in criteria arguments, with numbers list only.
Use wildcards to match partial text matches in the list.

Hope this article about How to count items in list in Excel is explanatory. Find more articles on Counting values in the table using formulas here. If you liked our blogs, share it with your fristarts on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Related Articles :

The COUNTIFS Function in Excel : Learn more about the COUNTIFS function in Excel here.

How to use the SUMPRODUCT function in Excel: Returns the SUM after multiplication of values in multiple arrays in excel.

COUNTIFS with Dynamic Criteria Range : Count cells selecting the criteria from the list of options in criteria cell in Excel using data validation tool.

COUNTIFS Two Criteria Match : multiple criteria match in different lists in table using the COUNTIFS function in Excel

COUNTIFS With OR For Multiple Criteria : match two or more names in the same list using the OR criteria applied on the list in Excel.

How to Use Countif in VBA in Microsoft Excel : Count cells with criteria using Visual Basic for Applications code in Excel macros.

How to use wildcards in excel : Count cells matching phrases in text lists using the wildcards ( * , ? , ~ )in excel

Popular Articles:

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Exceln : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.