How to quickly count the first instance only of values in Excel?
When working with large datasets in Excel, it is common to encounter lists containing both duplicate and unique values. Sometimes, you may want to count only the first occurrence of each value, treating subsequent duplicates as non-unique. For example, in a list where certain values appear multiple times, you might want a count that only considers each value's initial instance, similar to the scenario shown in the screenshot below. This task can help you quickly understand how many distinct entries are present as 'first occurrences,' regardless of how many times each value might repeat later on. How can this requirement be achieved efficiently in Excel?
- Count first instance of items with formula
- Count first instance of items with Kutools for Excel
- Count first instance of items with VBA code macro
Count first instance of items with formula
One straightforward approach to count the first instance of each value is to use an Excel formula. This method identifies which entries are the first appearance within the dataset and allows you to sum them for a final count.
Scenario and value: This solution is suitable if you are working with columns of data and want a formula-based, dynamic solution that updates automatically if your data changes. It does not require any add-ins or special permissions, making it suitable for most users. However, it requires you to add an extra column to your worksheet.
To get started, follow these steps:
1. Select a blank cell immediately beside the first value of your dataset (for example, if your data is in A1:A10, select cell B1). Enter the following formula:
=(COUNTIF($A$1:$A1,$A1)=1)+0Press Enter, and then drag the fill handle down alongside your entire data column to apply the formula to each row. The result will display "1" for rows that are the first instance of that particular value, and "0" otherwise. See the screenshots below for illustration:
Tip: In this formula, $A$1 refers to the first cell in your data range (change as appropriate), and $A1 refers to the current row. If your data does not start in A1, adjust the references accordingly. The combination of absolute and relative references ensures the correct counting behavior as you copy the formula down.
2. To sum the total number of first instances, select another blank cell (for example, below your new formula column) and enter:
=SUM(B1:B10)Press Enter to return the count. The range B1:B10 should match the cells where you entered the previous formula. Adjust the cell references if your data has a different length or your formula is in another column.
Additional Notes: This formula method provides a real-time updating count if values are changed, added, or deleted. Be mindful that if your data range changes (such as when new rows are inserted), the formula ranges may need expansion. Consider turning your data into an Excel Table for automatic formula propagation.
Count first instance of items with Kutools for Excel
If you have Kutools for Excel installed, you can take advantage of its Select Duplicate & Unique Cells utility to streamline the process, especially when dealing with large or complex data sets. This tool not only helps you count the first instance of values but can also highlight them for easy reference.
After freely installing Kutools for Excel, please proceed as follows:
1. Select all the cells in your range where you want to count the first instance (for example, A1:A10), then click Kutools > Select > Select Duplicate & Unique Cells in the ribbon. See screenshot below:
2. In the Select Duplicate & Unique Cells dialog box, select the All unique (Including 1st duplicates) option under the Rule section. If you wish, you can also choose to fill the selected cells with a distinctive background color or change their font color to make them stand out more easily.
3. When you click OK, a dialog box will pop up showing you the count of first instances within your selected range. This total includes both unique values and the first occurrence of duplicates. See the screenshot for reference:
4. Click OK to close the dialogs. The first instances of each item will now be selected and optionally highlighted, making them easy to distinguish within your worksheet.
Applicable scenarios and cautions: The Kutools method is highly efficient for users who regularly process large data tables or need to highlight results instantly. It avoids formula errors and reduces manual input. However, it requires the Kutools add-in to be installed on your system. Be sure to review cell selections before running the utility to ensure accurate results. If you wish to undo the highlighting, you can use Excel's Undo function (Ctrl + Z).
Count first instance of items with VBA code macro
For scenarios where you want the process to be fully automated, you can use a VBA macro to loop through the list and count the first occurrence of each value without manually adding formulas or relying on external add-ins. This is especially useful for repetitive tasks or large datasets. Please note that VBA macros require you to enable the Developer tab and save your file in a macro-enabled format (*.xlsm).
Applicability and notes: This macro is ideal for advanced users or those handling very large or frequently-updating datasets. As it makes direct changes, always back up your data before running. Macros may not work in web-based versions of Excel or if macros are disabled by your system's security settings.
1. In Excel, click Developer Tools > Visual Basic. When the Microsoft Visual Basic for Applications window appears, go to Insert > Module, and copy-paste the following code into the module window:
Sub CountFirstInstances()
Dim rng As Range
Dim dict As Object
Dim cell As Range
Dim firstInstanceCount As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the range to count first instances:", xTitleId, rng.Address, Type:=8)
Set dict = CreateObject("Scripting.Dictionary")
firstInstanceCount = 0
For Each cell In rng
If Not dict.exists(cell.Value) Then
dict.Add cell.Value, 1
firstInstanceCount = firstInstanceCount + 1
End If
Next cell
MsgBox "The number of first instances in the selected range is: " & firstInstanceCount, vbInformation, "First Instance Count"
End Sub2. After pasting the code, click the 
(Run) button or press F5 to execute the macro. When prompted, select the range you wish to analyze (for example, A1:A10) and press OK. A dialog box will pop up showing the count of first instances (unique and the first appearance of duplicated values) in your selection.
Tips and error prevention: If you make an incorrect selection, simply rerun the macro. The dictionary object used ensures that empty cells are also considered, so be attentive if your data range contains blanks, as this might add an extra count for empty cells. For better accuracy, avoid selecting empty rows or filter out blanks before use. VBA methods may encounter security warnings or require macro permissions; adjust your Trust Center settings if needed.
Troubleshooting advice and remarks: If your macro does not run, check whether macros are enabled under File > Options > Trust Center > Trust Center Settings > Macro Settings. Always save your work before running any code. This VBA code works for lists in a single column; for multi-column ranges, modify the code as needed.
Summary suggestions: In summary, the choice between formula, Kutools utility, or VBA macro depends on your skill level, dataset size, and preference for manual versus automated solutions. The formula method is flexible for small data and those comfortable with core Excel features; Kutools provides a quick and visual option for those with the add-in, while the VBA macro is best if you wish to automate repeated counts or operate on very large datasets. Each approach can help you effectively identify and tally the first occurrence of each value according to your workflow.
Related Articles:
- How to count the number of cells with data in Excel?
- How to count frequency of a text/number/character in Excel column?
- How to count the number of characters excluding spaces in Excel cell?
- How to count page breaks of active sheet in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in