Basic Excel Advanced Excel dtnguyen basic functions in excel, basic excel functions, how to use basic functions in excel 2010, manual for learning excel 2010

1

Excel COUNT function – counts cells with numbers:

2 Using the COUNT function in Excel – things to remember4 Excel COUNTIFS function – counts cells that match several criteria:

In this short tutorial Learn Excel Online will explain the basics of the Excel COUNT and COUNTA functions, and show some examples of using count formulas in Excel. You will also learn how to use the COUNTIF and COUNTIFS functions to count cells that meet one or more predefined criteria.

Watching: What is the counta function?

When using Excel, in addition to calculating values, you also need to count cells with values ​​– with any value or with specific types of values. For example, you might want to quickly count all items in a list, or the total number of inventory counts in a selected range.

Microsoft Excel provides a few special functions for counting cells: COUNT and COUNTA. Both of these functions are very simple and easy to use. So first, Learn Excel Online will analyze these two functions, and then give a few Excel formulas to count the number of cells that meet certain condition(s) and little hints when counting certain types of values. treat.

Great basic excel document sharing

Excel COUNT function – count cells with numbers:

You use the COUNT function in Excel to count the number of cells that contain numeric values.

The syntax of the Excel COUNT function is as follows:

COUNT(value1, , …)Where value1, value2,… are cell references or ranges in which you want to count cells by numbers.

In modern versions of Excel 2016, Excel 2013, Excel 2010, and Excel 2007, the COUNT function accepts up to 255 arguments. In previous versions of Excel, you could provide up to 30 ‘values’.

For example, the following formula returns the total number of numeric cells in the range A1:A100:

=COUNT(A1:A100)

Note. In the internal Excel system, dates are stored as serial numbers, and so the Excel COUNT function counts both dates and times.

Using the COUNT function in Excel – things to remember

Below are the operating rules of the Excel COUNT function.

Learn Word Excel Basics

Example COUNT formula in Excel

Here are a few examples of using the COUNT function in Excel with different values.

To count cells with numeric values ​​in a range, use a simple formula like =COUNT(A2:A10). The following screenshot illustrates what data types are counted and which are ignored:

To count several non-contiguous ranges, we need to include them all in the Excel COUNT formula. For example, to count cells with numbers in columns B and D, you can use a formula similar to this:

=COUNT(B2:B7, D2:D7)

Tip:

If you want to count numbers that meet certain criteria, use COUNTIF or COUNTIFS. If in addition to numbers, you want to count cells that contain text, logical values ​​and errors, then use the COUNTA function. count cells that contain values ​​(not counting empty cells) The COUNTA function in Excel counts the number of cells in the range excluding blank cells.

Commonly used functions in excel

The syntax of the Excel COUNTA function is the same as COUNT:

COUNTA (value1, , …) where value1, value2,… is the cell reference or range where you want to count cells that are not blank.

For example, to count cells with values ​​in the range A1:A100, use the following formula:

=COUNTA(A1:A100)

To count non-empty cells in some non-contiguous range, use a COUNTA formula similar to this:

=COUNTA(B2:B10, D2:D20, E2:F10)

As you can see, the ranges supplied to the Excel COUNTA formula don’t have to be the same size, which means that each range can contain a different number of rows and columns.

Please note that Excel’s COUNTA function counts cells that contain any data type, including:

– Number– Date/time– Text value– Boolean values ​​of TRUE and FALSE– Error values ​​such as #VALUE or # N/A– Empty text string (“”)

In some cases, you may be confused by the result of the COUNTA function because it is different from what you see with the naked eye. The problem is that an Excel COUNTA formula can count cells that visually appear to be empty cells, but are technically not. For example, if you accidentally type a space in a cell, that cell will also be counted. Or, if a cell contains some formula that returns an empty string, that cell will also be counted.

In other words, the only cells that the COUNTA function doesn’t count are completely empty cells.

The following screenshot shows the difference between the Excel COUNT and COUNTA functions:

Tips. If you just want to quickly count the non-blank cells in a selected range, just look at the Status Bar in the bottom right corner of the Excel window:

Excel COUNTIF function – counts cells that meet a given condition:

The COUNTIF function is used to count cells that meet a certain criteria. Its syntax requires 2 mandatory arguments:

COUNTIF (range, criteria) In the first argument, you specify a range in which you want to count cells. And in greed the second number, you specify a condition that should be met.

For example, to count how many cells in the range A2:A15 are “Apples”, you use the following COUNTIF formula:

=COUNTIF(A2:A15, “apples”)

If you type the criteria directly into the formula instead, you can enter a cell reference as shown in the following screenshot:

Excel COUNTIFS function – counts cells that match several criteria:

The COUNTIFS function is similar to COUNTIF, but it allows to specify multiple ranges and multiple criteria. Its syntax is as follows:

COUNTIFS(criteria_range1, range1, …)

The COUNTIFS function was introduced in Excel 2007 and is available in all later versions of Excel such as 2010, 2013 and 2016.

For example, to count how many “apples” (column A) made \$200 and more sales (column B), you use the following COUNTIFS formula:

=COUNTIFS(A2:A15,”apples”, B2:B15,”>=200″)

And again, to make your COUNTIFS formula more flexible, you can provide cell references as criteria:

Count the number of cells in a range (ROWS and COLUMNS functions)

If you need to find out the total number of cells in a rectangular range, use the ROWS and COLUMNS functions to return the number of rows and columns in an array, respectively:

=ROWS(range)*COLUMNS (range)

For example, to find out how many cells are in a certain range, like the range A1:D7, use the following formula:

=ROWS(A1:D7)*COLUMNS(A1:D7)

In addition, to further improve your knowledge of Excel, you can join the EX101 – Excel course from basic to advanced for working people of the Learning Excel Online system. In this course, you will learn complete and systematic knowledge about Excel’s functions and tools, and the application of those functions and tools to work. Currently, the system has many incentives for you to register for this course. For more information, see: Learn Excel Online