How to Count Specific Items in List in Excel

This post will guide you how to Count Cells that contain a apecific string of text using a formula in Excel 2013/2016 or Excel office 365.How do I create a count of the values that appear in a list or table in Excel. And this post will show you how to use the COUNTIFS function to count items in a list or table in Excel.

The COUNTIFS function extends the COUNTIF function which only allow one criteria. And the COUNTIFS function is similar with the SUMIFS function, which can be used to find the sum of all cells that match two or more criteria.

The Syntax of COUNTIFS function is as below:


Note: range is the first range of cells. Criteria1 is the criteria that be matched against range.

Range2 is the second range of cells that should be matched against criteria.

And if all of criteria is matched in those ranges then a cell is counted.

So you should see that COUNTIFS function works with a pair of range and criteria. And if the all of the given criteria matched, and then the function will return the count of all values in the given range.

Assuming that you have two range of cells A2:A6, and B2:B6. And each value in those two range is criteria. You can specify the below formula to count the number of items in those two ranges.


Note: the range A2:A6 is the first range and the criteria is A2. The formula would return a count of every value in range A2:A6.

The range B2:B6 is the second range and the criteria is B2. And this pair would return a count of every value in range B2:B6.

You can see that two different ranges should be used in the above formula.

Type this formula in Cell E2 to get the count of the first row. And then copy down the formula to get the counts of the other rows.

count specific items in list1

Related Functions

  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel COUNTIFS function
    The Excel COUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…
  • Excel SUMIFS Function
    The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…


Related Posts

If Cell Contain Specific Text

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of ...

Average Of Numbers With Multiple Criteria In Excel

Have you ever come across a task to calculate the average of the numbers with respect to multiple criteria? Are you tired of doing this cumbersome task manually? Are you willing to do this task smartly in just a matter ...

How to Sum if Equal to Many Items or A Range in Excel

If we want to sum numbers from a range with criteria, we often select SUMIF of SUMIFS functions as the first choice to create a formula. The criteria can be a number or an array or a collection of some ...

How to Sum if Equal to X or Y in Excel

In daily work, if we want to sum numbers from a range, and only sum the numbers which being equal to X or Y in the range, we can create a formula with Excel build-in functions to get the result. ...

How to Sum Last N Days in Excel

In our daily life, we may want to sum amounts or sales for a specific period, for example in last N days. Sum numbers in Excel is easy to run, we can apply SUM function. But if we want to ...