Excel – COUNTIFS function, conditional statistical function, examples and usage


You have learned the COUNT function and the conditional IF function, try a completely new function in Excel that is COUNTIFS function. The COUNTIFS function is a conditional statistic function in a list. Find out more details of the COUNTIFS function according to the instructions below of Taimienphi.vn.


Conditions in the COUNTIFS function can be characters, numbers, dates, … so you can apply flexibly to calculations in excel spreadsheets to achieve the highest work efficiency. Guide to using the COUNTIFS function is a basic lesson in the Excel Learning syllabus on Taimienphi.vn mentioned before.

Taimienphi.vn also summarizes advanced statistical functions including usage methods and specific functions, very complete for your reference: See the Statistical functions in Excel


The COUNTIFS function calculates statistics

The COUNTIFS function is also used a lot of different cases, but for newbies, in this article we will give the most specific, simplest example to readers.

INSTRUCTIONS FOR USING COUNTIFS IN EXCEL – illustrative example

Content:
1. Syntax of COUNTIFS function in EXCEL
2. Some notes when using the COUNTIFS function in EXCEL
3. Specific examples of the COUNTIFS function in EXCEL

1. Syntax of COUNTIFS function in EXCEL

The Excel COUNTIFS function returns the number of cells that meet one or more conditions. COUNTIFS can be used with conditions like dates, numbers, text, and other conditions.

COUNTIFS supports logical operators (>,,>, =) and wildcards (* ,?) for combining parts.

Syntax: COUNTIFS (Criteria_range1, Criteria1,[Criteria_range2,Criteria2], …)

Inside:

range1 – the first range to evaluate.
criteria1 – specify the first range to apply the first condition (criteria1), required.
range2 – [điều kiện bổ sung] second range to evaluate.
criteria2 – [điều kiện bổ sung] determine scope of application of the second condition.

Ham countifs in excel

2. Some notes when using the COUNTIFS function in EXCEL

COUNTIFS Counts the number of cells in the range that match the condition provided. Unlike the COUNTIF function, the COUNTIFS function can apply more than one condition to more than one range. The range and conditions apply in pairs, and only the first pair is required. For each additional condition, you must provide other range / condition pairs. Allow up to 127 range of conditions / conditions.

Note:

– Each additional data range must have the same number of rows and columns as the first data range (range 1). COUNTIFS can count adjacent and nonadjacent data regions. If the supplied data area does not match, you will receive the error # VALUE.

– Conditions that do not use numbers do not need to be enclosed in quotes, but a number condition does. For example, 100, “100”, “> 32”, “jim”, or A1 (where A1 contains a number).

– The wildcard? and * may be used under conditions. The question mark (?) Can match any character and the asterisk (*) match any sequence of characters.

– To find a question mark (?) Or asterisk (*), use the tilde (~) before the question mark before or the asterisk (ie ~?, ~ *).

The structure of the COUNTIFS function is quite different from Count function, COUNTIFS function can have 127 pairs of Criteria_range, Criteria

.Criteria_range is one or more numbers, names, arrays, or references that contain numbers.
Criteria is in the form of numbers, expressions, cell references, or defined text strings. Blank cells or cells containing text data are ignored.

Tips To count cells containing data in a spreadsheet, you use the Counta function

3. Specific examples of the COUNTIFS function in EXCEL

Example 1: For a transcript of some students, count the number of students scoring 9 and 8 in all 3 subjects:

Ham countifs in excel

To count, use the COUNTIFS formula: Count the number of students who get all 3 points 9 with the formula

The formula for cell D11 is: =COUNTIFS (D5: D9,9, E5: E9,9, F5: F9,9)

Ham countifs in Excel 2007

If you want to count the students who get all 3 points, 8, use the following formula:

The formula for cell D11 is: =COUNTIFS (D5: D9,8, E5: E9,8, F5: F9,8)

Ham countifs in Excel 2010

And you count the number of students who get all 3 points of 8 or 3 of 9 at the same time, similar to other points, you do the same. You can also filter different points.

So the article above has guided you to use functions COUNTIFS to statistic data with many conditions, so you can apply this function to calculations and statistics on Excel spreadsheets. Readers can see more articles also about counting functions, but a little different is counting the empty cells that is COUNTBLANK function to count the dragon cells, using the COUNTBLANK function is also very simple, see more ways Use the COUNTBLANK function in the following article nh

https://thuthuat.taimienphi.vn/ham-countifs-in-excel-962n.aspx

.

Add a Comment

Your email address will not be published. Required fields are marked *