Cập nhật nội dung chi tiết về **How To Use The Excel Countif Function** mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.

COUNTIF is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and COUNTIF requires a cell range, you can’t use an array.

COUNTIF only supports a single condition. If you need to apply multiple criteria, use the COUNTIFS function. If you need to manipulate values in the range argument as part of a logical test, see the SUMPRODUCT and/or FILTER functions.

Basic example

In the worksheet shown above, the following formulas are used in cells G5, G6, and G7:

=

COUNTIF

(

B5:B12,

"jim"

)

// count name = "jim"

=

COUNTIF

(

C5:C12,

"ca"

)

// count state = "ca"

Notice COUNTIF is not case-sensitive, “CA” and “ca” are treated the same.

Double quotes (“”) in criteria

In general, text values need to be enclosed in double quotes (“”), and numbers do not. However, when a logical operator is included with a number, the number and operator must be enclosed in quotes, as seen in the second example below:

=

COUNTIF

(

A1:A10,

100

)

// count cells equal to 100

=

COUNTIF

(

A1:A10,

"jim"

)

// count cells equal to "jim"

Value from another cell

A value from another cell can be included in criteria using concatenation. In the example below, COUNTIF will return the count of values in A1:A10 that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes.

=

COUNTIF

(

A1:A10,

"<"

&

B1)

// count cells less than B1

Not equal to

To construct “not equal to” criteria, use the “” operator surrounded by double quotes (“”). For example, the formula below will count cells not equal to “red” in the range A1:A10:

Blank cells

COUNTIF can count cells that are blank or not blank. The formulas below count blank and not blank cells in the range A1:A10:

=

COUNTIF

(

A1:A10,

""

)

// blank

Dates

The easiest way to use COUNTIF with dates is to refer to a valid date in another cell with a cell reference. For example, to count cells in A1:A10 that contain a date greater than the date in B1, you can use a formula like this:

The safest way hardcode a date into COUNTIF is to use the DATE function. This ensures Excel will understand the date. To count cells in A1:A10 that contain a date less than April 1, 2020, you can use a formula like this

=

COUNTIF

(

A1:A10,

"<"

&

DATE

(

2020

,

4

,

1

))

// dates less than 1-Apr-2020

Wildcards

The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in a A1:A5 that contain the text "apple" anywhere, you can use a formula like this:

=

COUNTIF

(

A1:A5,

"*apple*"

)

// cells that contain "apple"

To count cells in A1:A5 that contain any 3 text characters, you can use:

=

COUNTIF

(

A1:A5,

"???"

)

// cells that contain any 3 characters

The tilde (~) is an escape character to match literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).

Notes

COUNTIF is not case-sensitive. Use the EXACT function for case-sensitive counts.

COUNTIF only supports one condition. Use the COUNTIFS function for multiple criteria.

Cell references in criteria are not enclosed in quotes, i.e. "

The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters (zero or more).

To match a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).

COUNTIF requires a range, you can't substitute an array.

COUNTIF returns incorrect results when used to match strings longer than 255 characters.

COUNTIF will return a #VALUE error when referencing another workbook that is closed.

## How To Use The Excel Counta Function

Random list of names

At the core, this formula uses the INDEX function to retrieve 10 random names from a named range called “names” which contains 100 names. For example, to retrieve the fifth name from the list, we use INDEX like this…

Add row numbers and skip blanks

In the example shown, the goal is to add row numbers in column B only when there is a value in column C. The formula in B5 is:

=IF(ISBLANK(C5),””,COUNTA($C$5:C5))

The IF function first checks if cell C5 has…

Cell contains all of many things

The key is this snippet:

ISNUMBER(SEARCH(things,B5)

This is based on another formula (explained in detail here) that simply checks a cell for a single substring. If the cell contains the substring, the formula…

Count cells that are blank

The COUNTBLANK function counts the number of cells in the range that don’t contain any value and returns this number as the result. Cells that contain text, numbers, dates, errors, etc. are not counted. COUNTBLANK is…

Last row in mixed data with no blanks

This formula uses the COUNTA function to count values in a range. COUNTA counts both numbers and text to so works well with mixed data.

The range B4:B8 contains 5 values, so COUNTA returns 5. The number 5 corresponds…

Count unique values

This example uses the UNIQUE function to extract unique values. When UNIQUE is provided with the range B5:B16, which contains 12 values, it returns the 7 unique values seen in D5:D11. These are returned directly to the…

Dynamic named range with OFFSET

This formula uses the OFFSET function to generate a range that expands and contracts by adjusting height and width based on a count of non-empty cells.

The first argument in OFFSET represents the first cell in the…

Running count group by n size

The core of this formula is the COUNTA function, configured with an expanding range like this:

COUNTA($B$5:B5)

As the formula is copied down the column, the range starting with B5 expands to include each new row, and…

Count cells not equal to many things

First, a little context. Normally, if you have just a couple things you don’t want to count, you can use COUNTIFS like this:

But this doesn…

Project complete percentage

In this example if a task is marked “Done”, then it is considered complete. The goal is to calculate the percent complete for the project by showing the ratio of complete tasks to total tasks, expressed as a percentage…

Count sold and remaining

The COUNTA function counts non-blank cells that contain numbers or text. The first COUNTA counts non-blank cells in the range B5:B11 and returns the number 7:

COUNTA(B5:B11)

The second COUNTA function…

Generate random text strings

The new dynamic array formulas in Excel 365 make it much easier to solve certain tricky problems with formulas.

In this example, the goal is to generate a list of random 6-character codes. The randomness is handled by…

Sort by random

The SORTBY function allows sorting based on one or more “sort by” arrays, as long long as they have dimensions that are compatible with the data being sorted. In this example, there are 10 values being sorted, the…

Score quiz answers with key

This formula uses the named range “key” (C4:G4) for convenience only. Without the named range, you’ll want to use an absolute reference so the formula can be copied.

In cell I7, we have this formula:

=SUM(–(C7:G7=…

Reverse a list or range

The heart of this formula is the INDEX function, which is given the list as the array argument:

=INDEX(list

The second part of the formula is an expression that works out the correct row number as the formula is…

## How To Use The Excel Sumif Function

SUMIF is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and SUMIF requires a cell range for the range argument, you can’t use an array.

SUMIF only supports a single condition. If you need to apply multiple criteria, use the SUMIFS function. If you need to manipulate values that appear in the range argument (i.e. extract the year from dates to use in criteria) see the SUMPRODUCT and/or FILTER functions.

Videos

Basic usage

In the worksheet shown, there are three SUMIF examples. In the first example (G6), SUMIF is configured to sum values greater than 100. In the second example (G7), SUMIF returns the sum of values where the color is “red”. In the last example (G8), SUMIF is configured to sum values where the state is “CA” (California).

=

SUMIF

(

B6:B10,

"Jim"

,

D6:D10)

// Rep = Jim

=

SUMIF

(

C6:C10,

"ca"

,

D6:D10)

// State = CA

Notice the equals sign (=) is not required when constructing “is equal to” criteria. Also notice SUMIF is not case-sensitive. You can sum values where the Rep is Jim using “jim” or “Jim”.

Criteria in another cell

Not equal to

To express "not equal to" criteria, use the "" operator surrounded by double quotes (""):

Again notice SUMIF is not case-sensitive.

Blank cells

SUMIF can calculate sums based on cells that are blank or not blank. In the example below, SUMIF is used to sum the amounts in column C depending on whether column D contains "x" or is empty:

=

SUMIF

(

D5:D9,

""

,

C5:C9)

// blank

The best way to use SUMIF with dates is to refer to a valid date in another cell, or use the DATE function. The example below shows both methods:

=

SUMIF

(

B5:B9,

"<"

&

DATE

(

2019

,

3

,

1

),

C5:C9)

Wildcards

The SUMIF function supports wildcards, as seen in the example below:

=

SUMIF

(

B5:B9,

"mi*"

,

C5:C9)

// begins with "mi"

=

SUMIF

(

B5:B9,

"*ota"

,

C5:C9)

// ends with "ota"

=

SUMIF

(

B5:B9,

"????"

,

C5:C9)

// contains 4 characters

See below for more SUMIF formula examples.

Notes

SUMIF only supports one condition. Use the SUMIFS function for multiple criteria.

When sum_range is omitted, the cells in range will be summed.

Cell references in criteria are not enclosed in quotes, i.e. "

The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters (zero or more).

To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).

SUMIFS requires a range, you can't substitute an array.

## How To Use The Excel Subtotal Function

Use the SUBTOTAL function to get a subtotal in a list or database. Despite the name, SUBTOTAL has the ability to perform a variety of math functions, including AVERAGE, COUNT, MAX, and many others (see table below for the complete list). SUBTOTAL returns an aggregate result from a set of data. By default, SUBTOTAL excludes values in rows hidden by a filter, which makes SUBTOTAL very useful in Excel Tables.

The SUBTOTAL function automatically ignores other SUBTOTAL formulas that exist in references to prevent double-counting.

Examples

Below are a few examples of SUBTOTAL configured to SUM, COUNT, and AVERAGE the values in a range. Notice the only difference is the value used for the function_num argument:

=

SUBTOTAL

(

109

,

range)

// SUM

=

SUBTOTAL

(

103

,

range)

// COUNT

=

SUBTOTAL

(

101

,

range)

// AVERAGE

Available calculations

SUBTOTAL behavior is controlled by the function_num argument, which is provided as a numeric value. There are 11 functions available, each with two options, as seen in the table below. Notice the values are “paired” (e.g. 1-101, 2-102, 3-103, and so on). This is related to how SUBTOTAL deals with manually hidden rows. When function_num is between 1-11, SUBTOTAL includes cells that have been manually hidden. When function_num is between 101-111, SUBTOTAL excludes values in rows that have been manually hidden.

Function Include hidden Ignore hidden

AVERAGE 1 101

COUNT 2 102

COUNTA 3 103

MAX 4 104

MIN 5 105

PRODUCT 6 106

STDEV 7 107

STDEVP 8 108

SUM 9 109

VAR 10 110

VARP 11 111

Note: SUBTOTAL always ignores values in cells that are hidden with a filter. Values in rows that have been “filtered out” are never included, regardless of function_num.

SUBTOTAL in Excel Tables

The SUBTOTAL function is used when you display a Total row in an Excel Table. Excel inserts the SUBTOTAL function automatically, and you can use a drop-down menu to switch behavior and show max, min, average, etc. The reason Excel uses SUBTOTAL for calculations in the Total row of an Excel Table is because SUBTOTAL automatically excludes rows hidden by the filter controls at the top of the table. That is, as you filter rows in a table with a Total row, you’ll see the calculations update automatically to respect the filter.

SUBTOTAL with outlines

Note: although the Outline feature is an “easy” way to insert subtotals in a set of data, a Pivot Table is a better and more flexible way to analyze data. In addition, a Pivot Table will separate the data from the presentation of the data, which is a best practice.

Notes

When function_num is between 1-11, SUBTOTAL includes values that are hidden

When function_num is between 101-111, SUBTOTAL excludes values that are hidden

In filtered lists, SUBTOTAL always ignores values in hidden rows, regardless of function_num.

SUBTOTAL ignores other SUBTOTAL formulas that exist in references to prevent double-counting.

SUBTOTAL is designed to work with vertical data values arranged vertically. In horizontal ranges, values in hidden columns are always included.

Bạn đang đọc nội dung bài viết **How To Use The Excel Countif Function** trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!