COUNTIFS function in excel is a statistical function. This function is used to count the number of cells that meet a certain one or more criteria. It can be used to count cells containing dates, numbers, text etc. provided they match certain criteria. This function also supports logical operators and wildcards. This is a worksheet function and can be entered into the cell in which count is required.
=COUNTIFS(Range1, Criteria1, Range2, Criteria2,……)
Range1: It is the 1st range of cells which will be used for counting.
Criteria1: It determines which cells will be counted out of the 1st range.
Range2: It is the 2nd range of cells which will be used for counting.
Criteria2: It determines which cells will be counted out of the 2nd range.
- COUNTIFS function can use up to 255 additional values.
- If one wants to count text values and logical values, use COUNTA function.
- COUNTIF and COUNTIFS function are used if one wants to use certain criteria for counting
- If only number values are to be counted use COUNT function.
In this example, various values are entered in Column A and Column B, and COUNTIFS function is used to count values of Column A having criteria (greater than 5) and column B having criteria(less than 100) using the formula =COUNTIFS(A4:A9, “>5”, B4:B9, “<100”) which gives 2 as the result.