What is the COUNTIFS function in Excel?
→ The Microsoft Excel COUNTIFS function counts the number of cells in a range, that meets single or multiple criteria.
→ In simple language, COUNTIFS_function helps us to count cells that fulfill defined single or multiple criteria.
→ We can use logical operators like (Greater Than (>), Less Than (<), Not equal to something (<>), Equal to (=)), and wildcards operator the asterisk (*) = character matches zero or more characters, question mark(?) = character matches with single characters for defining the criteria with COUNTIFS function.
→ The criteria may be the exact match or it may be the partial match. In both conditions, this function will use.
→ This function returns a numeric value.
→ We can put COUNTIFS Function in a Statistical Function Category
➨ How to use the COUNTIFS function in Excel?
→ Refer to this article for the COUNTIFS function explained with examples. We need to enter a simple formula in MS Excel spreadsheets for counting the cells based on single or multiple conditions fulfill.
What is the syntax of the COUNTIFS function?
→ The syntax for the COUNTIFS function in Microsoft Excel is:
=COUNTIFS(range1, criteria1, [range2], [criteria2], ..., [range_n], [criteria_n],)
➨ The Syntax Parameter or Syntax Arguments are:
→ range1 – This is the cell range from which we need to count the cells.
→ criteria1 - It is used to determine which cells to be counted. criteria1 is applied against criteria_range1.
→ range2 ... range_n – This is an optional command if we want to add more than one criterion then it will be used. It is the range of cells that you want to apply criteria2 ... criteria_n.
→ criteria2, ... criteria_n – This is an optional command. It is used to determine which cells to be counted. criteria2 is applied against criteria_range2, criteria3 is applied against criteria_range3, and so on.
→ We can add up to 127 range/criteria pairs.
Examples of COUNTIFS Function:
→ For a better understanding of the COUNTIFS function, we can take one example.
→ In the below picture, we can see the different cars sold in different years.
→ So we want to count the number of cells that having the Toyota cars were sold in 2017.
→ In this example, we have multiple criteria for comparison so we will use the COUNTIFS function.
Example:
→ We need to count the cell qty that contains Toyota Car sold in the year 2017.
→ So, from the syntax, we will use the below formula for the making count of the total Toyota car sold in the year 2017.
=COUNTIFS(B2:B10,"Toyota",A2:A10,"2017")
➨ From the above formula,
1. range1 = B2:B10 (Check criteria1 “Toyota” from this range.)
2. criteria1 = “Toyota”
3. range2 = A2:A10 – (Check criteria2 “2017” from this range.)
4. criteria2 = “2017”
So, we will get result of the formula =COUNTIFS(B2:B10,"Toyota",A2:A10,"2017") = 3.
→ Also, we can use cell reference instead of the criteria’s absolute value in the above formula as mentioned in the below formula.
=COUNTIFS(B2:B10,D3,A2:A10,D4)
→ So we have used cell reference (D3) instead of absolute value (Toyota) and used to mention D4 instead of "2017" in the above example.
👉 For a regular update:
➨ Join us (Telegram Channel)
➨ Join us (LinkedIn Page)
👉 Also Navigate Our Popular Category:
➨ Lookup & Reference Functions
Post a Comment