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:
=COUNTIFS(range,criteria1,range2,criteria2,…)
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.
=COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,B2)
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.
Table of Contents
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], …)…