Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.
Count how often a value occurs
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 More...Less
Suppose you want to find out how many times particular text or a number value occurs in a range of cells. For example:
-
If a range, such as A2:D20, contains the number values 5, 6, 7, and 6, then the number 6 occurs two times.
-
If a column contains "Buchanan", "Dodsworth", "Dodsworth", and "Dodsworth", then "Dodsworth" occurs three times.
There are several ways to count how often a value occurs.
Use the COUNTIF function to count how many times a particular value appears in a range of cells.
For more information, see COUNTIF function.
The COUNTIFS function is similar to the COUNTIF function with one important exception: COUNTIFS lets you apply criteria to cells across multiple ranges and counts the number of times all criteria are met. You can use up to 127 range/criteria pairs with COUNTIFS.
The syntax for COUNTIFS is:
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)
See the following example:
To learn more about using this function to count with multiple ranges and criteria, see COUNTIFS function.
Let's say you need to determine how many salespeople sold a particular item in a certain region or you want to know how many sales over a certain value were made by a particular salesperson. You can use the IF and COUNT functions together; that is, you first use the IF function to test a condition and then, only if the result of the IF function is True, you use the COUNT function to count cells.
Notes:
-
The formulas in this example must be entered as array formulas.
-
If you have a current version of Microsoft 365, then you can simply enter the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula.
-
If you have opened this workbook in Excel for Windows or Excel 2016 for Mac and newer versions, and want to change the formula or create a similar formula, press F2, and then press Ctrl+Shift+Enter to make the formula return the results you expect. In earlier versions of Excel for Mac, use
+Shift+Enter.
-
-
For the example formulas to work, the second argument for the IF function must be a number.
+Shift+Enter.
To learn more about these functions, see COUNT function and IF function.
In the examples that follow, we use the IF and SUM functions together. The IF function first tests the values in some cells and then, if the result of the test is True, SUM totals those values that pass the test.
Notes:The formulas in this example must be entered as array formulas.
-
If you have a current version of Microsoft 365, then you can simply enter the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula.
-
If you have opened this workbook in Excel for Windows or Excel 2016 for Mac and newer versions, and want to change the formula or create a similar formula, press F2, and then press Ctrl+Shift+Enter to make the formula return the results you expect. In earlier versions of Excel for Mac, use
+Shift+Enter.
Example 1
The above function says if C2:C7 contains the values Buchanan and Dodsworth, then the SUM function should display the sum of records where the condition is met. The formula finds three records for Buchanan and one for Dodsworth in the given range, and displays 4.
Example 2
The above function says if D2:D7 contains values lesser than $9000 or greater than $19,000, then SUM should display the sum of all those records where the condition is met. The formula finds two records D3 and D5 with values lesser than $9000, and then D4 and D6 with values greater than $19,000, and displays 4.
Example 3
The above function says if D2:D7 has invoices for Buchanan for less than $9000, then SUM should display the sum of records where the condition is met. The formula finds that C6 meets the condition, and displays 1.
You can use a PivotTable to display totals and count the occurrences of unique values. A PivotTable is an interactive way to quickly summarize large amounts of data. You can use a PivotTable to expand and collapse levels of data to focus your results and to drill down to details from the summary data for areas that are of interest to you. In addition, you can move rows to columns or columns to rows ("pivoting") to see a count of how many times a value occurs in a PivotTable. Let's look at a sample scenario of a Sales spreadsheet, where you can count how many sales values are there for Golf and Tennis for specific quarters.
-
Enter the following data in an Excel spreadsheet.
-
Select A2:C8
-
Click Insert > PivotTable.
-
In the Create PivotTable dialog box, click Select a table or range, then click New Worksheet, and then click OK.
An empty PivotTable is created in a new sheet.
-
In the PivotTable Fields pane, do the following:
-
Drag Sport to the Rows area.
-
Drag Quarter to the Columns area.
-
Drag Sales to the Values area.
-
Repeat step c.
The field name displays as SumofSales2 in both the PivotTable and the Values area.
At this point, the PivotTable Fields pane looks like this:
-
In the Values area, click the dropdown next to SumofSales2 and select Value Field Settings.
-
In the Value Field Settings dialog box, do the following:
-
In the Summarize value field by section, select Count.
-
In the Custom Name field, modify the name to Count.
-
Click OK.
-
The PivotTable displays the count of records for Golf and Tennis in Quarter 3 and Quarter 4, along with the sales figures.
-
Need more help?
You can always ask an expert in the Excel Tech Communityor get support inCommunities.
See Also
Find and correct errors in formulas
Excel keyboard shortcuts and function keys
Need more help?
Want more options?
Discover Community
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
Thank you for your feedback!
×
I'm an enthusiast with a deep understanding of Microsoft Excel and its various functions, formulas, and features. My expertise in this area comes from years of experience using Excel for data analysis, reporting, and complex calculations in both professional and personal contexts. I have also provided training and support to individuals and organizations seeking to maximize their productivity with Microsoft Excel.
Now, let's delve into the concepts used in the article you provided:
-
COUNTIF Function: The article mentions using the COUNTIF function to count how many times a particular value appears in a range of cells. COUNTIF is a built-in Excel function that takes two arguments: a range of cells and a criteria. It counts the number of cells within the range that meet the specified criteria.
-
COUNTIFS Function: The article introduces the COUNTIFS function, which is similar to COUNTIF but allows you to apply multiple criteria to cells across different ranges. COUNTIFS is particularly useful when you need to count occurrences based on multiple conditions.
-
IF Function: The article discusses using the IF function in combination with the COUNT function to count cells based on a condition. The IF function is used to test a condition and returns one value if the condition is true and another if it's false.
-
SUM Function: The SUM function is mentioned in conjunction with the IF function. It's used to add up values in a range of cells. In the examples provided, SUM is used to calculate the total of records that meet specific conditions.
-
Dynamic Array Formulas: The article notes that some of the formulas must be entered as array formulas. Dynamic array formulas allow you to perform calculations on multiple cells and return multiple results. This functionality is available in newer versions of Excel.
-
PivotTable: The article discusses using a PivotTable to display totals and count the occurrences of unique values. A PivotTable is a powerful tool in Excel for summarizing and analyzing data. It allows you to create customized reports by dragging and dropping fields to organize data in rows and columns, perform calculations, and count occurrences.
These concepts are essential for anyone working with Excel to efficiently manage and analyze data. If you have any specific questions or need further clarification on any of these concepts or how to use them in Excel, feel free to ask!
