Tag Archives: COUNTIF

excel count formula

Excel Count Cells with Text and Formula – Excel Stats

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

In Excel we often like to Count Things. Sometimes those things are Cells with Text, Formulas or Formatting. Other times we want to Count Blank or Non-Blank Cells… and so on. Today I will teach you All About Counting Things in Excel. Excel Count Functions are an obvious option to go with, but there are also wonders you can do with just 1 line of VBA Code.

If want to see a Cool Summary of all your Excel Workbook Statistics scroll down to the last section.

Excel Count Functions

First let us start with Basic Excel Count Functions:

Excel COUNT function

Counts Number of Cells with Numbers

COUNT Function

Excel COUNTA function

Counts Number of Non-Blank Cells

COUNTA Function

Excel COUNTBLANK function

Counts Number of Blank Cells

COUNTBLANK Function

Excel COUNTIF function

Counts Number of Cells that Fulfill an If Condition

COUNTIF Function

Excel DCOUNT function

Counts Number of Cells that Fulfill MULTIPLE Specified Conditions

DCOUNT Excel Function

VBA Count Functions

VBA Count Cells in Range

To simply count the number of VBA Cells in an Excel Range use the Count Range property.

To learn more about the VBA Range read my VBA Range Tutorial

VBA Count Cells with Numbers

To Count Cells with Numbers (equivalent to Excel COUNT Function) use the WorksheetFunctions.Count function:

VBA Count Non-Blank Cells

To Count Non-Blank Cells (equivalent to Excel COUNTA Function) use the WorksheetFunctions.CountA function:

VBA Count Blank Cells

To Count Blank Cells (equivalent to Excel COUNTBLANK Function) use the WorksheetFunctions.CountBlank function or the SpecialCells Range property:
CountA:

SpecialCells:

If SpecialCells returns no cells then the Count property will throw an error

VBA Count If Cells

To Count If Cells (equivalent to Excel COUNTIF Function) on certain conditions use the WorksheetFunctions.CountIf function:

VBA Count Cells with Formulas

To Count Cells with Formulas use the SpecialCells Range property:

If SpecialCells returns no cells then the Count property will throw an error

VBA Count Cells with Constants (Non-Formulas & Non-Blank)

To Count Cells with Constants use the SpecialCells Range property:

If SpecialCells returns no cells then the Count property will throw an error

VBA Count Visible / Invisible Cells

To Count Cells with Constants use the SpecialCells Range property:
Count Visible Cells:

Count Invisible Cells:

VBA Count Other Types of Cells

The SpecialCells Range property allows you to find a subset of certain types of cells. Here are all the available types:

Constant Description
xlCellTypeAllFormatConditions Any type of Cells
xlCellTypeAllValidation Cells with Validation Criteria
xlCellTypeBlanks Blank Cells
xlCellTypeComments Cells with Comments
xlCellTypeConstants Cells with Constants (Non-Formula & Non-Blank)
xlCellTypeFormulas Cells with Formulas (beginning with a =)
xlCellTypeLastCell Get Last Cell in the UsedRange
xlCellTypeSameFormatConditions Cells with Common Format Conditions
xlCellTypeSameValidation Cells with Common Validation Criteria
xlCellTypeVisible All Visible Cells

Excel Function Usage Statistics

I saved best for last. What if you wanted to do a statistic on the Excel Functions used in your Excel Workbook? Excel doesn’t provide you with a neat statistics windows like Word for words and sentences. Fortunately, I developed a neat VBA Macro that Counts every Function used in all Excel Formulas and presents a neat report as a result.

The Code

The Code below will generate Excel Function Usage Statistics to a new Worksheet.

How to use it? Simply run the CreateStats Sub procedure:

Or go to the DEVELOPER Ribbon Tab, select Macros and Run the CreateStats procedure on the list.

An Example

Example Excel WorkbookSay we have an example Excel Workbook with various Formulas. The CreateStats procedure will produce the following statistics:

Excel Workbook Function Usage Statistics
Excel Workbook Function Usage Statistics

An Excel UserForm

Want a neat UserForm report like the one below?

Excel Workbook Function Usage Statistics
Excel Workbook Function Usage Statistics

Instead of using the CreateStats function you can alternatively use my CreateStatsUserForm function (using the StatsForm – see Download section below), to instead admire a neat statistics UserForm without having to manage any additional ad-hoc Worksheets.

Download Excel Stats

You can download the Excel Functions Usage Statistics Modules below:

how to find duplicates in excel

How to Find Duplicates in Excel. Remove Duplicates in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.00 out of 5)
Loading...

How to find duplicates in Excel? Today we will explore this question in and out. We will start first by understanding How to Highlight Duplicates in Excel. Our second objective will be learning How to Remove Duplicates. I will explore several approaches to this task – using the Data ribbons Remove Duplicates feature, but also showing how we can use MS Queries to remove duplicates and also do additional filtering/sanitizing of our data set.

CONTENTS

Find Duplicates in Excel

find duplicates source table
Example Table with Duplicates
There are several ways to go around Finding or Highlighting duplicates in Excel:

Following today’s post we will use the example Excel Table on the right.

1. Find Duplicates using Excel Formulas

To Find Duplicates in Excel using Formulas we will use the COUNTIF Excel formula to verify if a certain row as been repeated above. This will work only on a single column (our unique column identifier). Scroll down to the end to learn how to check for duplicates based on all three columns.

Add Is Duplicate? Column to your Worksheet

Add a Is Duplicate? Column to your Worksheet. In our example assuming that the Last column uniquely identifies records in my table input the following formula in the first cell of your new Is Duplicate? Column:

find duplicates in excel using formulas
Add COUNTIF Excel Formula to Find Duplicates

Excel Formula:

=IF(COUNTIF(B$1:B1;"="&B2);"DUPLICATE";"")

Drag the Formula down to all Cells

Drag the COUNTIF Formula from the First Cell down to the Last.
find duplicates in excel the steps

Instead of dragging the Formula down manually you can:

  1. Select the First Cell with the Formula
  2. Copy by Formula by hitting CTRL+C
  3. Select all cells down by hitting CTRL+
  4. Paste the Formula by hitting CTRL+P

Find Entire Row Duplicates

Did you notice that in the previous example 2 rows were in fact not duplicates? See below:

find duplicates in excel for an entire row
False Duplicates as Last name is not unique!

What should you do if there is no Column that Uniquely identifies your rows? We need to use a Helper Column! See Column D below:
find duplicates in excel with a helper column
Add a Helper Column that Concatenates All Columns

Simply add the Helper Column and Concatenate all other Columns like so:

=A2&"_"&B2&"_"&C2

Next you need to use the COUNTIF Formula on the Helper Column instead.

I recommend separating columns with some Separator (such as the _) as otherwise you may again get False Positive Duplicates

2. Highlight Duplicates using Conditional Formatting

highlight duplicates conditional formatting
Conditional Formatting in HOME Ribbon Tab
To Highlight Duplicates in Excel we need to use the Conditional Formatting feature in the HOME Ribbon Tab in Excel. Follow the steps below:
highlight duplicates conditional formatting
How to Highlight Duplicates in Excel using Conditional Formatting

Select the Column to Highlight Duplicates

highlight duplicates select columnSelect the Column in which you want to Highlight your Duplicates. As a Tip you can Select the First Cell and hit CTRL+END.

Open Conditional Formatting

highlight duplicates conditional formattingGo to the HOME Excel Ribbon Tab and select Conditional Formatting.

Create New Rule

highlight duplicates create new ruleAs we want to Highlight Duplicate Cell Values in Excel we need to create a new Conditional Formatting Rule. Click New Rule.

Select Duplicate formatting

highlight duplicates formatting
Select the Format only unique or duplicate values option to define the Formatting of Duplicate Values.

Select Duplicate formatting

In the new window make sure Format All is set to duplicate. Next set the formatting for your Duplicates.
highlight duplicates format
Once completed you should see your Duplicate Cells Highlighted in your Excel spreadsheet.
highlight duplicates in excel

Remove Duplicates in Excel

Having answered the question of How to Find Duplicates in Excel, but what you often want to do is Remove Duplicates. So lets now tackle the issue of How to Remove Duplicates in Excel.

Similarly as with Finding Duplicates, there several ways to go around Removing Duplicates:

Remove Duplicates with Data Ribbon

Fortunately Removing Duplicates is a typical scenario in Excel supported by the Data Ribbon Tab.
remove duplicates in excel

Select Excel Table

remove duplicates select tableSelect your Entire Excel Table by hitting CTRL+A.

Remove Duplicates

remove duplicates data ribbonGo to the Data Ribbon Tab in Excel and select Remove Duplicates.

Select Columns with Duplicates

remove duplicates select columns with duplicatesIn this last step you need to Select All Columns in which there are Duplicates. If you just want to remove rows in which there are duplicates in a single column – remember to select only that Column.
Once you finish hit OK and done!

Remove Duplicates with MS Query

Microsoft Query is a great tool to run MS Queries (SQL) on Excel data.

If you haven’t use MS Queries read my post on How to Create an MS Query in Excel or check-out my Excel SQL AddIn.

The DISTINCT SQL statement filters only the DISTINCT rows within a TABLE. You can use it on any SQL SELECT Query. See my example below:
remove duplicates ms query
The table on the Left is the Source Table the table on Right is the Result Table. This is the Microsoft Query you can use to filter only DISTINCT rows:

Where replace Sheet1 with the name of your Worksheet.

Count Duplicates in Excel

Similarly as with the previous case, there several ways to go around Counting Duplicates:

Count Duplicates using Excel Formulas

count duplicates in excelTo Count Duplicates in Excel follow the same steps as in Find Duplicates using Excel Formulas except you need to use the following Excel Formula using COUNTIF for the example in this post:

Similarly as in Find Entire Row Duplicates using Excel Formulas to Count Entire Rows that are duplicates, follow the above steps and create a similar Helper Column as such:

Count Duplicates using MS Query

Microsoft Query is a great tool to run MS Queries (SQL) on Excel data.

If you haven’t use MS Queries read my post on How to Create an MS Query in Excel or check-out my Excel SQL AddIn.

We can easily use a Microsoft Query to count only the duplicate records within our Data Set:
count duplicates in excel ms query
Here is the MS Query for the above Table: