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!