Tag Archives: formula

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:

cascading drop-down

Excel Cascading drop-down (no VBA!)

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

Cascading drop-down are a very useful feature in Excel making it much easier to categorize your records. Say you have a list of records you want to associate with categories and subcategories. Normally you would start by assigning a category to each record and then have a problem with matching a subcategory.

What is a cascading drop-down?

Cascading drop-downs are drop-downs that filter their values based on the selection made on another drop-down (higher in the hierarchy). You can have any amount of cascading drop-downs e.g. drill-down from store department, through the products lane, down till the product family and finally the product SKU.
See the example below:
cascading dropdown
What to play with a online example? Telerik controls have a nice mock-up Cascading DropDownList for this.

How to make a cascading drop-down

Now down to business. Let’s go through the simple steps of creating a 1 level cascading drop-down consisting of 2 drop-downs. Simply repeat these steps to create additional cascading drop-downs levels.

Prepare source data for the drop-downs

I prepared a simple data set consisting of countries and associated cities.

Cascading dropdown: Source worksheet
Cascading dropdown: Source worksheet

Notice that the countries are repeated for each city. This is because we need to map out each city with an individual country for what we are about to do in the next step. If you need to add an additional level for the cascading drop-down you should repeat this approach.

Create a named range for each category

Create a named range for each country by selecting all cities within the country. Repeat this for all remaining countries. In case you need to add new subcategories (cities) to your cascading drop-down insert rows in the middle of a country section. This will automatically extend the named range.

Now for the the important part!!! Remember to name your countries using the exact name of the country. In case there are spaces in the names (which are not allowed in named ranges) replace them with “_”. This post handles such cases equally.

Cascading drop-down: Creating the named ranges
Cascading drop-down: Creating the named ranges

Create a unique list of unrepeating items for the first drop-down

You can either create this list manually or to just listing the countries manually for the first drop-down. I personally prefer to have the list created automatically based on the first column of countries in the examples above – use the formula below for that:

It’s best to use the same worksheet as the source tables and use the formula below to extract a unique repeating list of countries. This is an Array Formula so remember to hit CTRL+SHIFT+ENTER when editing the formula (first line only). Then simply drag/copy it down to get all countries.

Cascading drop-down: First drop-down
Cascading drop-down: First drop-down

Array Formula for unique list of unrepeating countries
=IFNA(INDEX(OFFSET($A$1;1;0;COUNTA($A:$A)-1);MATCH(0;COUNTIF(E$1:$E1;OFFSET($A$1;1;0;COUNTA($A:$A)-1));0));"")

Create a named range for the first drop-down

Assuming your list of countries is in column E of the same worksheet as shown above feel free to use the formula below for the named range – it will update the list automatically whenever you add new countries. Alternatively simply select the whole list of countries – but remember to update the named range manually when adding new countries.

=OFFSET(Dictionary!$E$1;1;0;COUNTA(Dictionary!$E:$E)-1)

This is an example using the formula above:

Cascading drop-down: Named range for list of countries
Cascading drop-down: Named range for list of countries

Create the cascading drop-down based on the named ranges using Data Validation

You are almost there! Now go to the worksheet where you want to define your cascading drop-down. And add Data Validation for the first and second cell as shown below:

First drop-down
Go to Data Validation and define the list based on the named range we created in Step 4:

Cascading drop-down: First drop-down
Cascading drop-down: First drop-down

Second drop-down
Now let’s repeat the same exercise and again for the second cell we need to add Data Validation. This time we will use the INDIRECT function to dynamically associate the correct named range of cities based on the country selected in the first cell.

Assuming the first drop-down is defined in cell A2. Set the following formula for your Data Validation:

=INDIRECT(SUBSTITUTE($A2;" ";"_"))

See example below:

Cascading drop-down: Second drop-down
Cascading drop-down: Second drop-down

That’s it this is the final result:
Cascading drop-down: Result
Cascading drop-down: Result

Download the example

Feel free to download the example used above:

Follow me on Twitter or Facebook for more Tips!

distinct list

Dynamic Distinct Column in Excel using Array Formulas

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

I see that often many users have issues with managing lists of values and translating them to unique/distinct lists where values do not repeat themselves. There are many way to tackle this problem and you would be surprised that there even is a formula to handle this task. Let’s dive into this subject.

What we have (unsorted list) Vs. What we want (distinct list of sorted values)

Let’s assume a simple table of data. Where column A is full of repeating Names and column B is a list of corresponding values. We will be working on this data set through out this post. This data set is a typical example of the issue we often have with repeating rows of indistinct data. What we will want to do is to somehow summarize this table with a list of distinct Names and aggregated Values.

What we have

Let’s assume we have a simple table of Names and Values. What we will want to do is to somehow summarize this table with a list of distinct Names and aggregated Values.

Name (A column) Value (column B)
Tom 60
Matthew 98
James 19
John 16
Matthew 45
John 26
John 70
James 60

What we want

The result of this operation should look somewhat like this:

Name Value
James 79
John 112
Matthew 143
Tom 60

Now let’s familiarize with 2 approaches to this issue.

Method 1: Distinct column using a PivotTable

The most obvious solution is of course to create a PivotTable from this data set. All we need to do is add the Names as ROWS and Values as VALUES to the Pivot to get a simple summary with the exact data we need. See below:

Distinct list: Pivot Table to aggregate Values
Pivot Table to aggregate Values

Method 2: Distinct column using Array Formulas

Now putting aside the obvious, in some cases Pivots are not the answer. Especially if we don’t want to use VBA Macros and want to create a dynamic table which will simply update itself with the latest Values and Names.

Provide list of distinct Names

The first issue which we stumble upon is to somehow produce a list of distinct Names. At first this may seem impossible to be done by a Excel function. Fortunately again Array Formulas can come in handy in this task. This site features the elegant solution to this problem which I will try to explain in much detail.

To provide a list of distinct Nameswe must use the following formula:

=INDEX(A$1:A$9;MATCH(0;COUNTIF(E$1:E1;A$1:A$9);0))

This is the final outcome when we hit CTRL+SHIFT+ENTER to make the formula an Array Formula and drag it down:

Distinct list: Distinct column of Names
Distinct column of Names

Now let’s ponder for a second on how the formula works as it might not be so straightforward as it seems:

'Gets an item from A$1:A$9 with an index provided by the MATCH function
=INDEX(
       A$1:A$9;
'Find value "0" in the column of value provided by the COUNTIF function
       MATCH(
            0;
'Returns an array. Count items from array A$1:A$9 if they are provided
'on the E$1:E1 list
            COUNTIF(
'Notice that this list has only 1 static item. The first item is the 
'anchor, however, the second item will move to include all rows above
'the current one
                   E$1:E1;
                   A$1:A$9
                   );
            0)
      )

The exciting thing is how the MATCH function is used above. Usually the MATCH function is provided with a range of cells. This time, however, we are providing it with an array being the result of the COUNTIF function. What the MATCH function does is search for the first item resulting from the COUNTIF that has 0 counts i.e. it has not yet been provided in the list.

Now the formula will start producing #N/A errors when dragged beyond the number of distinct elements in the Names columns. You can correct this by wrapping it with a IFERROR function:

=IFERROR(
        INDEX(A$1:A$9;MATCH(0;COUNTIF(E$1:E1;A$1:A$9);0))
        ;"")

Sort the list of distinct Names

Now that we know how to provide a list of distinct names we still need to make sure that the list is sorted alphabetically else it will be provided in the same order as the items on the initial list. Here we need again to resort to Array Formulas to help us with this tasks.

Let’s start with explaining how to get the index of each name (concept from Chandoo.org):

Get index of elements in our unsorted distinct list
Get index of elements in our unsorted distinct list

Notice the code in column E:

=COUNTIF(D$2:D$5;"<"&D2)

What we are doing is counting, for each distinct name, the number of items alphabetically of lower lexicographical value than our the current Name (D2).

Let’s now consider an Array Formula based on this:

=COUNTIF(D$2:D$5;"<"&D$2:D$5)

If we hit F9 we will see that an Array Formula would evaluate to:

={3;2;0;1}

This is the correct sequence of our list of distinct items.

If we combine this with an INDEX-MATCH combo we can iterate through this sequence:

Sorted list of distinct Names
Sorted list of distinct Names

'Gets an item from D$2:D$5 with an index provided by the MATCH function
=INDEX(D$2:D$5;
'Find value of the current ROW-2 (this is simply to sequence through 
'the arrary) in the column of value provided by the COUNTIF function
               MATCH(
                    ROW()-2;
'Evaluates to {3;2;0;1} - this is the correct sequence of our distinct
'array of Names
                    COUNTIF(D$2:D$5;"<"&D$2:D$5);
                    0)
      )

Summary

Now to summarize what we have.

  • First, we provided the distinct list of Names in column D
  • Secondly, we provided a separate column which sorts the distinct Names in column D
  • Lastly, if we add a simple SUMIF function as shown below we can sum all values for each distinct Name in the Names column
Final data table
Final data table

Next Steps

Check out other similar posts:
EXCEL: Dynamic row numbers
EXCEL: 10 Top Excel features
EXCEL: Split columns on any pattern

dynamic row numbers

Dynamic Row Numbers in Excel

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

Today I want to elaborate shortly on how to correctly and easily number rows in Excel by adding dynamic row numbers using simple formulas. Every neat data table in Excel should have a numbering column in place so that every row can be easily reference at least by the item number. One way of numbering rows is to simply input the numbers and drag them down. However, this is a static manner and the numbering will not refresh automatically if you change the places of any rows or add/delete rows. Here I want to introduce several easy ways to achieve nice and neat dynamic row numbering in Excel. Ok so let’s dive right into 3 methods for achieving nice dynamic row numbers in Excel.

Method 1 – dynamic row numbering with w/o skipping empty rows

Let’s say we want to dynamically number rows counting also every empty row in between i.e. if there is an empty row between the numbers we want the numbering to account for that an increase the index. I use this approach most often due to it’s simplicity. See the example below on how this works.

Method 1 - Dynamic row numbers w/o skipping rows
Method 1 – Dynamic numbering w/o skipping rows

The formula is very simple:

=ROW()-ROW(A$1)

A$1 is simply the header of the column to guarantee we start numbering from 1. Easy and neat right?

Method 2 – Dynamic numbering skipping empty rows

This time let’s account for every empty row in between. We want to continue the numbering from the last index. This comes in handy when you have sections of data or if you group the rows into different headers but want to retain the right numbering. See the example below:

Method 2 - Dynamic row numbers with skipping empty rows
Method 2 – Dynamic numbering skipping empty rows

Again the formula is dead simple:

=COUNTA(C$1:C1)

We use the static $ marker to make the range start from always the first cell in the column. The formula will count all non-blank rows so will skip any blanks we leave in between.

Dynamic nested numbering

Now for a bonus – dynamic nested numbering. Sometimes we need to add numbering with nested indices. I certainly encourage using nested numbering as it makes many tables more clear to read and the grouping more obvious. The approach/formula below can easily be reused to support nesting of additional levels. Numbering nested indices manually is often a nightmare if we need to frequently rearrange rows or add/delete some rows in between. Unfortunately, no formula will know for us when to automatically increment the first few nested indices (e.g. 1.2.1 to 1.3.1), but we can automatically increment the last index in the nested numbering index. See below.

Dynamic nested row numbering
Dynamic nested numbering

We need to input manually add the first index e.g.

'Cell F2
1.1

Then below we can now use an automatic formula that we do the increment for us:

'Cell F3
=LEFT(F2;FIND(".";F2))&RIGHT(F2;LEN(F2)-FIND(".";F2))+1

We can repeat this process with the next indices e.g. 2.1, 3.1 etc. We will always need to type the first one manually but the formula can help renumber the subsequent indices automatically e.g. 2.2,2.3,2.4 etc.

Hope this helps you with generating those dynamic row numbers!