How To Count The Number Of Rows In Excel

Excel Count Functions

Meet vii ways to count in Excel. Examples and video tutorials evidence how to count Excel cells with numbers, text, blanks, or cells that contain specific words or other criteria

Overview: 7 Ways to Count in Excel

To see a quick overview of 7 means to count in Excel, spotter this short video. Or, sentinel the Slide Show: 7 Ways to Count, further down on this folio.

Get the 7 Ways to Count sample workbook, so yous tin can follow forth with the video.

Count Numbers, All Data, or Blank Cells

This video shows the COUNT function, the COUNTA office, and the COUNTBLANK function, and warns of the quirks in counting some types of cells.

Written instructions are below the video. Count Cells with Numbers, Count Cells with Data and Count Blank Cells. Also, get the sample workbook, to see all the formulas.

Count Cells with Numbers -- COUNT

The COUNT office will count cells that contain numbers. Its syntax is:
=COUNT(value1, value2,...value30).
The arguments (e.g. value1) tin be cell references, or values typed into the COUNT formula.

The post-obit COUNT function case uses i argument -- a reference to cells A1:A5.

1. Enter the sample data on your worksheet
2. In prison cell A7, enter an COUNT formula, to count the numbers in cavalcade A:=COUNT(A1:A5)



3. Printing the Enter key, to consummate the formula.
4. The event will be 3, the number of cells that incorporate numbers.
   Cell A1 isn't counted, because it contains text.

Note: Since dates are stored as numbers, the COUNT function will include any cells that contain dates.

Count Cells with Information -- COUNTA

The COUNTA function will count cells that are non empty. (See annotation on blank cells below)

Its syntax is:
=COUNTA(value1, value2,...value30).
The arguments (e.g. value1) can be cell references, or values typed into the formula. The following example uses one argument -- a reference to cells A1:A5.

1. Enter the sample data on your worksheet
2. In cell A7, enter a COUNTA formula, to count the numbers in column A:=COUNTA(A1:A5)



3. Press the Enter key, to consummate the formula.
4. The result will be 4, the number of cells that contain information.

Count Bare Cells -- COUNTBLANK

The COUNTBLANK function will count cells that are empty. (Meet annotation on blank cells below)

Its syntax is:
=COUNTBLANK(range).
The post-obit case uses a reference to cells A1:A5.

1. Enter the sample data on your worksheet
2. In cell A7, enter a COUNTBLANK formula, to count the numbers in column A:=COUNTBLANK(A1:A5)



3. Press the Enter key, to complete the formula.
4. The result will exist 1, the number of empty cells.

Cells That Look Bare

Both COUNTA and COUNTBLANK will count cells with formulas that look empty, if the formula result is an empty string {""). The empty string is treated as text, and counted.

For example, =IF(B2="","",B2).

Both COUNTA and COUNTBLANK will likewise count cells which had formulas that returned an empty string, but and then were converted to values, past copying and pasting equally values.

• NOTE: You lot can see apostrophes in those "non-blank" converted cells, if you add a check mark to Excel Options, Avant-garde, Lotus Compatibility, "Transition navigation keys."

To fix cells that await blank, but are being counted, run into my Contextures weblog: Fix Blank Excel Cells Copied From Database▲TOP

Count cells that match one criterion

This video shows how to use the Excel COUNTIF role to count cells that contain a specific cord of text, such as "Pen". You can as well notice text that is part of a cell -- how many orders were placed for whatsoever kind of pen, such as "Gel Pen", "Pen" or even a "Pencil"?

The written instructions are below the video: Match benchmark exactly and Match benchmark in a string

Friction match one criterion exactly -- COUNTIF

In Excel, count cells that run into a specific criterion. In this example only the Pen orders will be counted.

1. Select the cell in which you desire to see the count (cell A12 in this example)
2. Type an equal sign (=) to first the formula
3. Type:   COUNTIF(
4. Select the cells that contain the values to check for the criterion. In this example, cells A1:A10 will be checked
5. Type a comma, to separate the arguments
6. Blazon the benchmark. In this example, you're checking for text, and so type the give-and-take in double quotes:   "Pen"
   Notation: upper and lower case are treated equally
   
7. Type a closing bracket
   The completed formula is: =COUNTIF(A1:A10,"Pen")
   



8. Printing the Enter key to complete the entry
9. The result will be 4, the number of cells that contain "Pen"▲TOP

Lucifer criterion in a string

In Excel, count cells that contain a criterion equally part of the cell's contents. In this example all Pen, Gel Pen, and Pencil orders volition exist counted, considering they contain the cord "pen".

1. Select the cell in which yous want to meet the count (cell A12 in this case)
2. Blazon an equal sign (=) to start the formula
3. Blazon:   COUNTIF(
4. Select the cells that contain the values to check for the criterion. In this example, cells A1:A10 will be checked
5. Type a comma, to split up the arguments
6. Type the criterion. In this case, you're checking for text, and then type the give-and-take in double quotes, with 1 or more than asterisk (*) wildcard characters:   "*Pen*"
   Note: upper and lower instance are treated as
   
7. Type a closing bracket
   The completed formula is: =COUNTIF(A1:A10,"*Pen*")
   



8. Printing the Enter key to complete the entry
9. The result will be 6, the number of cells that contain the cord, "Pen"▲TOP

Annotation: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in stride 7 higher up could be changed to:
=COUNTIF(A1:A10,"*" & B12 & "*")
if cell B12 contained the text — pen.

COUNTIF and COUNTIFS Warnings

There are a few things to be careful with, when using the COUNTIF and COUNTIFS functions.

• Count might include false duplicates
• Specific numbers within other numbers are non counted - use other functions
• Cannot count text strings longer than 255 characters
• Similar text might be counted, instead of just specific text - see the example hither

False Duplicates With COUNTIF

Be careful when using COUNTIF, because it matches numbers, with text that looks like numbers, and that could issue in fake counts.

For example, if you have a text entry -- "00123" -- it would be counted every bit a duplicate for the number -- 123.

=COUNTIF($B$two:$B$ten,B2))>1

If your data could contain entries similar that, use SUMPRODUCT, instead of COUNTIF.

=SUMPRODUCT(--($B$2:$B$10=B2))>i

Character Limit of 255

COUNTIF and COUNTIFS can but bank check strings upward to 255 characters. Here's a simple example to show the problem. This formula counts how many times an item appears in the Item column:

• =COUNTIF([Item],[@Item])

In row 5, there is a #VALUE! error, because the text in cell C1 is longer than 255 characters.

Use a Different Function

To avert the problem with the 255 grapheme limit, use the SUMPRODUCT function, instead of COUNTIF or COUNTIFS. Hither is the revised formula:

• =SUMPRODUCT(--([Detail]=[@Detail]))

Here's how that SUMPRODUCT formula works:

• Checks each row [Item] to encounter if information technology'southward equal to the entry in the current row [@Item]
• The upshot is Truthful or Simulated for each row (highlighted in the screen shot below -- I pressed F9 to evaluate that department of the formula)
• The ii minus signs (double unary) change TRUE to one and Imitation to 0
• SUMPRODUCT adds upwards the 1s and 0s.

Microsoft Workaround Does Not Piece of work

On Microsoft's COUNTIF page, it says y'all can work effectually the 255 character limit, by joining two long strings with the concatenate operator (&). Here's an instance:

• =COUNTIF(A2:A5,"long string"&"another long string")

That proffer does Non piece of work for me !

Count Numbers in Numbers

The COUNTIF function can find specific letters or numbers in a text string. However, COUNTIF cannot notice a specific number within a real number.

In the screen shot beneath, there are 4 items in the list that incorporate a "ane". The COUNTIF formula in cell A9 gives an incorrect consequence of 3. It does not count the "1" in jail cell A2, because that cell contains a real number, not a text string.

Utilise Observe or SEARCH

Although the COUNTIF function can't count specific numbers within real numbers, the Notice or SEARCH function volition locate them.

In the screen shot below, the post-obit formulas are used in columns C, D and East, to look for a "1".

The ISNUMBER part returns Truthful or False, and the two minus signs (double unary) catechumen True to i and False to 0 (zippo).

• C2: =COUNTIF(A2,"*1*")
• D2: =--ISNUMBER(FIND(1,A2))
• E2: =--ISNUMBER(SEARCH(1,A2))

The Detect and SEARCH formulas found all 4 items that comprise a "i". The COUNTIF formula only institute iii items.

Apply SUMPRODUCT with FIND or SEARCH

Instead of checking each row individually, use the SUMPRODUCT function with Discover or SEARCH, to become the full count for the list.

• Use FIND to count numbers, or case-sensitive letters
• Use SEARCH to count numbers, or upper and lower case messages (non instance-sensitive)

Example ane: Numbers

In this example, the formulas give a count of cells that comprise a one. The effect is 4 in both cases.

• A9:    =SUMPRODUCT(--ISNUMBER(Discover(1,$A$2:$A$7)))
• A10:  =SUMPRODUCT(--ISNUMBER(SEARCH(1,$A$2:$A$7)))

Instance two: Letters

In the next case, the formulas give a count of cells that contain "a". Detect only counts the lower-example "a" (1), and SEARCH counts both the upper-case "A", and lower-instance "a" (two).

• A9:   =SUMPRODUCT(--ISNUMBER(FIND("a",$A$2:$A$vii)))
• A10: =SUMPRODUCT(--ISNUMBER(SEARCH("a",$A$2:$A$7)))

Count Cells "Less Than" or "Greater Than"

In this video, see how to use COUNTIF function to count the number of items in a list that are over or under a specific corporeality. Written instructions are below the video:

• Greater Than or Equal To
• Between v and 10 (In a range)
• Choose the COUNTIF Operator from a List

Count Cells Greater Than or Equal to

You can utilize an operator with a benchmark. In this example merely the rows where the quantity is greater than or equal to x will exist counted.

1. Select the jail cell in which y'all want to see the count (cell A12 in this case)
2. Type an equal sign (=) to beginning the formula
3. Type:   COUNTIF(
4. Select the cells that contain the values to check for the criterion. In this instance, cells B1:B10 volition exist checked
5. Type a comma, to separate the arguments
6. Blazon the criterion. In this example, you're checking for rows where the quantity is greater than or equal to 10. The >= operator is used before the number, and the entire criterion is enclosed in double quotes: ">=10"
   Annotation: Even though this is a numerical benchmark, it must enclosed in double quote marks.
   
7. Type a closing bracket
8. The completed formula is:
   =COUNTIF(B1:B10,">=10")
   
9. Press the Enter fundamental to consummate the entry

   

Note: Instead of typing the criterion in a formula, yous tin can refer to a cell. For case, the formula in pace 8 to a higher place could be changed to:
=COUNTIF(B1:B10,">=" & B12)
if cell B12 independent the number — ten.

Or, you could use a office as part of the criterion. For example:
 =COUNTIF(A1:A10,"<"&TODAY())▲TOP

Count Cells Between five and ten

You can combine COUNTIF formulas, to count rows that are inside a range of values. In this instance, the formula will count rows where the quantity is between 5 and 10 (inclusive).

1. Select the cell in which y'all want to see the count (prison cell A12 in this example)
2. Type a formula to count rows greater than or equal to v:
   =COUNTIF(B1:B10,">=5")
   
3. Type a minus sign
4. Type a formula to count rows greater than 10:
   COUNTIF(B1:B10,">10")
   
5. The completed formula is:
   =COUNTIF(B1:B10,">=5")-COUNTIF(B1:B10,">10")
   
6. Press the Enter key to complete the entry

   

Note: Instead of typing the criterion in a formula, you can refer to a prison cell. For example, the formula in step 8 above could exist inverse to:
=COUNTIF(B1:B10,">=" & B12) -
COUNTIF(B1:B10,">" & C12)
if cell B12 contained the number — 5 and prison cell C12 contained the number — x.▲TOP

Modify Operator for COUNTIF Function

Instead of typing the operator into the COUNTIF formula, as shown above, you can create a list of all possible operators, and select one from a drop downwardly listing. Then, refer to that operator in the formula.

This video shows the steps for setting up the formula, and the written instructions are below the video.

Create a Drop Downwardly Listing of Operators

To create a drop downward listing operators:

1. On a different sheet in the workbook, blazon a list of operators in a column.
2. Select the cells in the listing, and name that list as OpList.

   

3. On the main sheet, select the prison cell where you want the drop down list -- cell E6 in this example
4. Utilize the data validation command to create the drop downward listing, based on the named range -- OpList

Use the Drop Downwards List

Modify your COUNTIF formula, to replace the typed operator with a reference to the cell with the driblet down listing.

=COUNTIF(B2:B11,E6&F6)

And so, select ane of the operators from the drop down listing in cell E6, and the formula result will change.

Count cells that match multiple criteria

This video shows how to utilise the COUNTIFS role to count cells based on multiple criteria.

Written instructions are below the video:

--Count multiple criteria with COUNTIFS

--Count multiple criteria with SUMPRODUCT

--Count all dates in a specific month and year

Count Cells that Friction match multiple criteria

In Excel 2007 and later versions, you can employ the COUNTIFS part to count rows that come across ii or more criteria. In this instance just the rows where the item is "Pen" and the quantity is greater than or equal to ten volition be counted.

1. Select the cell in which yous want to see the full
2. Type an equal sign (=) to start the formula
3. Type:COUNTIFS(
4. Select the cells that contain the values to check for the first criterion. In this example, cells A2:A10 will be checked
5. Blazon a comma, and the beginning benchmark:"Pen"
   Note: Considering this is a text criterion, it is enclosed in double quote marks.
   
6. To first the adjacent ready of criteria, blazon a comma
7. Select the cells that contain the values to cheque for the second criterion. In this example, cells B2:B10 will be checked
8. Blazon a comma, and the second criterion:">=10"
   Note: Because this benchmark includes operators, information technology is enclosed in double quote marks. To count rows where the quantity is equal to ten, but the number x would exist required.
   
9. Finish with a endmost bracket: )
10. The completed formula is shown below.
    
11. Printing the Enter key to complete the entry

Note: Instead of typing the criterion in a formula, you can refer to a prison cell, equally shown in the second formula below. If using operators, enclose them in double quote marks.

Utilise typed criteria:

=COUNTIFS(A2:A10,"Pen",B2:B10,">=10")

or jail cell references:

=COUNTIFS(A2:A10,D3,B2:B10,">=" & E3)

Count cells that friction match multiple criteria -- SUMPRODUCT

In this example, the SUMPRODUCT function is used to count the rows where the item is "Pen" and the quantity is greater than or equal to ten. This solution volition work in older versions of Excel, where there COUNTIFS function is non available.

1. Select the cell in which you lot want to see the total
2. Type an equal sign (=) to start the formula
3. Type:   SUMPRODUCT(--(
4. Select the cells that comprise the values to cheque for the beginning criterion. In this example, cells A2:A10 will be checked
5. Type the starting time criterion:   ="Pen"
   Notation: Because this is a text criterion, it is enclosed in double quote marks.
   
6. Type ),--(
7. Select the cells that incorporate the values to check for the second benchmark. In this example, cells B2:B10 will be checked
8. Type the 2nd criterion:   >=10
   Note: Because this is a numerical benchmark, information technology isn't enclosed in double quote marks.
   
9. Finish with closing brackets: ))
10. The completed formula is shown below.
    
11. Press the Enter key to complete the entry

Note: Instead of typing the criterion in a formula, you can refer to a prison cell, as shown in the second formula below▲TOP

Utilize typed criteria:

=SUMPRODUCT(--(A2:A10="Pen"),--(B2:B10>=ten))

or cell references:

=SUMPRODUCT(--(A2:A10=D2),--(B2:B10>=E2))

Count All Dates in Specific Month and Year

In this instance, there is a date in cell A2, and the club list has dates in cell A5:A26. The post-obit SUMPRODUCT function is in cell D2, and information technology counts all the dates that have the same month and year as the date in cell A2.

=SUMPRODUCT((Calendar month(A5:A26)=Month(A2))*(YEAR(A5:A26)=YEAR(A2)))

• The MONTH office returns the month number for each cell, and compares that to the month number for the date in cell A2.
• The Yr part returns the year number for each cell, and compares that to the year number for the date in cell A2.
• But the rows where both the month and year match cell A2 are counted.

Count Duplicated Items in List

In this instance, there is a formula to check a column that should contain unique values merely. It volition alarm y'all if whatsoever of the values have been duplicated.

In cells A6:C12, there is a named table (tblIDs). In the ID column, each number should be unique, merely two is entered twice, and three is entered twice.

The formula shown below will count how many unique values have been duplicated.

• In this instance, the formula effect should be 2 -- there are 2 unique values that have been duplicated - two and 3.
• The formula will NOT tell us that 4 rows contain duplicated ID numbers.

Formula to Count Duplicated Items

Indistinguishable ID numbers could cause problems, so we'll create a formula to check for them. To count the duplicated values, enter this formula in cell A4. The details are below:

=SUMPRODUCT((tblIDs[ID]<>"") /
COUNTIF(tblIDs[ID],tblIDs[ID]&"") -
(COUNTIF(tblIDs[ID],tblIDs[ID])=1))

NOTE: To simply highlight indistinguishable values in a column, utilise Conditional Formatting.

How It Works

The SUMPRODUCT formula contains 3 formulas:

• A: (tblIDs[ID]<>"") - Check if the prison cell is NOT empty - TRUE (1) or Faux (0)
• B: COUNTIF(tblIDs[ID],tblIDs[ID]&"") - How many times does the ID announced in the cavalcade
• C: (COUNTIF(tblIDs[ID],tblIDs[ID])=1) - Number (B) is equal to one - TRUE (1) or Faux (0)

Next, A is divided past B, and C is subtracted

Finally, those results are summed, to give the count of duplicated values.

Calculate in the Formula Bar

In the formula bar, you can select each formula (A, B, and C), and printing F9 to summate that formula. This screen shot shows the results.

And then, calculate the A/B portion of the formula, to see these results. TRUE is equal to 1, and then i/1 equals 1, and i/2 equals 0.5.

Next, calculate A/B - C, to run across these results. TRUE is 1 and Faux is cypher, and then 1-1 equals nothing, and 0.5 - 0 equals 0.5:

Finally, the SUMPRODUCT function gives the sum of those numbers, with the result of 2.

Worksheet Formulas

You lot can come across the same results if yous put each part of the formula on the worksheet, and calculate each row separately.

In this screen shot, you tin can see the A and B results, and then A/B. Adjacent, run into the C calculations, and A/B-C. At the bottom of the final column, the sum is shown.

Use the Formula Result

In other parts of your workbook, you tin can refer to cell A4 (DupIds), to create alert messages, or show a zero, instead of the expected results. For example, show a message with a formula like this:

• =IF(DupIDs>0,"Before standing, remove indistinguishable IDs.","")

Or, multiply past 1 (True) or cypher (FALSE) in other formulas, based on the number in the DupIDs cell.

• =COUNT(tblIDs[ID])*--(DupIDs=0)

Count Rows in a Filtered List

After you filter the rows in a list, you can use functions to count simply the visible rows.

• For a simple count of visible numbers or all visible information, employ the SUBTOTAL role
• To count visible data, and ignore errors, use the AGGREGATE role
• To count specific items in a filtered Listing, use a SUMPRODUCT formula
• To count UNIQUE items in a filtered Listing, employ an Array-entered formula

Count Rows in a Filtered List With SUBTOTAL

After you filter the rows in a list, you tin use the SUBTOTAL role to count the visible rows.

1. Employ an AutoFilter to the tabular array. In that location are instructions hither -- AutoFilter Basics
2. Filter at least one of the columns in the tabular array. In this example, the beginning column has been filtered for Binders.
3. Select the cell immediately below the column yous want to sum.
4. Click the AutoSum button on the Excel's Standard toolbar.
   • If y'all desire the SUBTOTAL part in a cell other than the one directly below the filtered list, you tin type the formula, instead of using the AutoSum push.
5. A SUBTOTAL formula will exist automatically inserted, totalling the visible cells in the column
• The offset argument in the SUBTOTAL function is a function number, that specifies how the numbers should be calculated. The default is 9, which tells Excel to SUM the numbers.
• Other function numbers tin be used, such every bit ane for AVERAGE, and iii for COUNTA. Look in Excel's Help for a consummate listing.
6. To Count all the not-empty cells in column D, use a 3 every bit the first statement:
   =SUBTOTAL(3,D2:D10)
   
7. Press the Enter key to complete the formula entry.

   

Notation: To subtotal rows which have been either manually subconscious or filtered, use 103 as the role number, instead of three:
=SUBTOTAL(103,D2:D10)
▲TOP

Count Rows in Filtered List With Amass

After you filter the rows in a list, you tin can use the AGGREGATE role to count the visible rows. This function was introduced in Excel 2010, and is like to SUBTOTAL, simply it has 19 functions, compared to SUBTOTAL'southward 11 functions. Another reward is that it tin can ignore errors, likewise equally hidden rows.

1. Utilise an AutoFilter to the table. There are instructions hither -- AutoFilter Basics
2. Filter at least one of the columns in the table. In this example, the offset cavalcade has been filtered for Binders.
3. Select the cell in which you want to meet the total -- jail cell B1 in this example
4. To start the formula, type: =Amass(
5. In the listing of role numbers, double-click on 3-COUNTA, so blazon a comma
6. In the list of option numbers, double-click on 3 - Ignore hidden rows, error values, nested SUBTOTAL and Aggregate functions, and then type a comma
7. Select the cells that contain the values to bank check for the first benchmark. In this example, the Total cavalcade in the table is selected.
8. Type a endmost bracket, and so press the Enter key to complete the formula entry.
   =AGGREGATE(iii,3,Table1[Full])

The two visible numbers are counted, and the error in cell D9 is ignored.

Count Specific Items in a Filtered List

Laurent Longre created a formula that lets you piece of work with visible rows after a filter. For information see, Power Formula Technique in this article at John Walkenbach'southward web site (via the WayBack Motorcar site):
https://web.archive.org/web/20100110043824/ https://j-walk.com/ss/excel/eee/eee001.txt

Incorporating that technique, SUMPRODUCT can be used to count visible items in a filtered table. In the post-obit case, column D has been filtered for amounts greater than 100. The following formula will count the number of visible rows that contain "Pen" in column A.

1. From the drib down list in cell D1, select Custom.
2. Filter for rows greater than 100.
3. In cell A12, type: Pen
4. In cell B12, enter the post-obit formula:

=SUMPRODUCT(SUBTOTAL(iii,Get-go(A1:A10,ROW(A1:A10)
-MIN(ROW(A1:A10)),,1)), --(A1:A10=A12))

5. Press the Enter fundamental to complete the formula entry.▲Tiptop

Count Unique Items in a Filtered List

In the Excel Skillful Newsletter (event 20, July 8, 2001 - no longer available), there is a formula to count unique items in a filtered listing. In this example, the listing is filtered for the Central region, and unique items in column D are counted.

1. The LineVal column is a named range -- Rge
2. The name unRge is defined with the formula: =IF(SUBTOTAL(3,OFFSET(Rge,ROW(Rge)-MIN(ROW(Rge)),,i)),Rge,"")
3. In cell C2, enter the following formula:

=SUM(N(IF(ISNA(MATCH("""",unRge,0)),MATCH(Rge,Rge,0),
IF(Lucifer(unRge,unRge,0)=MATCH("""",unRge,0),0,
Match(unRge,unRge,0)))=ROW(Rge)-MIN(ROW(Rge))+1))

5. This is an array formula, so press Ctrl+ Shift + Enter to consummate the formula.▲TOP

Get the COUNT Sample Files

1. Download the Count Functions sample workbook. The zipped file is in xlsx format, and does not incorporate any macros.
2. Download the 7 Ways to Count sample workbook, to follow along with the 7 Ways to Count video. The zipped file is in xlsx format, and does non contain any macros.
3. Download the Character Limit 255 workbook. The zipped file is in xlsx format, and does not incorporate whatever macros.
4. Download the Count Unique Items in Filtered Listing workbook. The zipped file is in xlsx format, and does not contain any macros.
5. Download the Count Duplicate Number Sets workbook. Discover duplicate sets of 6 numbers, which tin exist in whatsoever order in the row. The zipped file is in xlsx format, and does non incorporate whatever macros.

More than Function Tutorials

Date Range, Sum or Count

Calculation Options

Count Criteria in Other Column

Count Specific Items

Count Specific Items in Cell

Count Cells With Specific Text

AVERAGE

SUM / SUMIF

Subtotal Feature

Source: https://www.contextures.com/xlfunctions04.html

Posted by: fultonvellut.blogspot.com

Share this post