How Many Days in a Row Can You Work Out

Count how often a value occurs

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the spider web Excel 2021 Excel 2022 for Mac Excel 2019 Excel 2022 for Mac Excel 2016 Excel 2022 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 case:

If a range, such as A2:D20, contains the number values 5, half-dozen, seven, and six, and then the number 6 occurs ii times.
If a cavalcade contains "Buchanan", "Dodsworth", "Dodsworth", and "Dodsworth", then "Dodsworth" occurs three times.

There are several means to count how often a value occurs.

Use the COUNTIF office to count how many times a item value appears in a range of cells.

COUNTIF examples

For more information, see COUNTIF function.

The COUNTIFS function is similar to the COUNTIF function with one important exception: COUNTIFS lets yous apply criteria to cells across multiple ranges and counts the number of times all criteria are met. Y'all tin can use up to 127 range/criteria pairs with COUNTIFS.

The syntax for COUNTIFS is:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)

Come across the following example:

COUNTIFS example

To learn more than about using this function to count with multiple ranges and criteria, see COUNTIFS function.

Permit's say you lot need to determine how many salespeople sold a item item in a sure region or y'all want to know how many sales over a sure value were made past a item salesperson. You tin can use the IF and COUNT functions together; that is, you offset use the IF office to test a condition and then, just if the outcome of the IF function is True, you use the COUNT office to count cells.

Notes:

The formulas in this example must be entered as array formulas.
- If y'all have a current version of Microsoft 365, then you tin simply enter the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic assortment formula.
- If yous have opened this workbook in Excel for Windows or Excel 2022 for Mac and newer versions, and want to change the formula or create a similar formula, press F2, and so printing Ctrl+Shift+Enter to make the formula return the results you expect. In earlier versions of Excel for Mac, use +Shift+Enter.
For the case formulas to work, the second statement for the IF part must be a number.

Examples of nested COUNT and IF functions

To learn more than about these functions, see COUNT function and IF office.

In the examples that follow, nosotros 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 accept a current version of Microsoft 365, so you tin can but enter the formula in the peak-left-cell of the output range, then printing ENTER to ostend the formula equally a dynamic array formula.
If you have opened this workbook in Excel for Windows or Excel 2022 for Mac and newer versions, and want to modify the formula or create a like 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

Example 1: SUM and IF nested in a formula

The higher up function says if C2:C7 contains the values Buchanan and Dodsworth, then the SUM function should display the sum of records where the status is met. The formula finds 3 records for Buchanan and one for Dodsworth in the given range, and displays 4.

Instance 2

Example 2: SUM and IF nested in a formula

The above function says if D2:D7 contains values lesser than $9000 or greater than $19,000, and 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

Example 3: SUM and IF nested in a formula

The above function says if D2:D7 has invoices for Buchanan for less than $9000, then SUM should brandish the sum of records where the condition is met. The formula finds that C6 meets the status, and displays ane.

You can utilize a PivotTable to display totals and count the occurrences of unique values. A PivotTable is an interactive way to apace summarize big amounts of information. You tin use a PivotTable to expand and plummet levels of information to focus your results and to drill down to details from the summary data for areas that are of interest to you lot. In add-on, you tin motion rows to columns or columns to rows ("pivoting") to see a count of how many times a value occurs in a PivotTable. Let's wait at a sample scenario of a Sales spreadsheet, where you tin can count how many sales values are in that location for Golf game 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, and so click New Worksheet, and and then click OK.

An empty PivotTable is created in a new sheet.
In the PivotTable Fields pane, practise the following:
1. Drag Sport to the Rows surface area.
2. Drag Quarter to the Columns expanse.
3. Drag Sales to the Values area.
4. Repeat stride c.
  
  The field name displays as SumofSales2 in both the PivotTable and the Values area.
  
  At this signal, the PivotTable Fields pane looks like this:
5. In the Values surface area, click the dropdown side by side to SumofSales2 and select Value Field Settings.
6. In the Value Field Settings dialog box, exercise the following:
  1. In the Summarize value field past department, select Count.
  2. In the Custom Name field, modify the name to Count.
  3. Click OK.
The PivotTable displays the count of records for Golf and Tennis in Quarter 3 and Quarter 4, along with the sales figures.