How to Use the Countif & Countifs Functions in Excel
Excel 2013's CountIf and CountIfs functions tabulate the number of records that meet defined criteria. Both functions work similarly, but the more robust CountIfs function supports multiple ranges and criteria. For example, if you want to count the number of times sales exceeded $100 over a given time period, you could use the simple CountIf function. However, if you want to count the number of times a specific employee produced a sale that exceeded $100, you'd need the CountIfs function.
Using the CountIf Function
Enter this formula in a blank cell:=CountIf(range,criteria)
Change the "range" value to match the data you wish to evaluate. For example, to check sales figures from C1 to C99, insert "C1:C99" in place of "range." To reference a range on a different worksheet, use "SheetName!C1:C99" instead. Entering "[WorkbookName]SheetName!C1:99" references a range in a different workbook.
Replace "criteria" with the assessment. Whether you're working with numbers or text, the criteria, including the operator and value, should be surrounded in quotes. If the assessment is a simple "equals" comparison, you can omit the equals sign. In the previous example, to look for sales that exceed $100, change the formula to:=CountIf(C1:C99,">100")
Using the CountIfs Function
Enter the same starting formula you used in the CountIf example, but use "CountIfs" instead. Absent additional criteria, the CountIfs function works exactly like the CountIf function and produces the same results, so the following formula is perfectly valid:=CountIfs(C1:C99,">100")
Add a comma before the closing parenthesis and enter the range of the secondary criteria. In the example, if you also want to check the employee name in column A, append the formula (keeping in mind it's still incomplete) as:=CountIfs(C1:C99,">100",A1:A99)The second range must contain the same number of rows and columns as the first range, but they don't have to be in adjacent columns or begin on the same row number.
Add a comma after the second range and enter new criteria. In the example, to check if the employee name is "Doe, John," change the formula to:=CountIfs(C1:C99,">100",A1:A99,"Doe, John")This formula only increments the counter when the column C record is greater than 100 and the column A record equals "Doe, John." When two ranges are included, Excel doesn't necessarily compare data on the same row number; but rather compares relative records. For example, if the second range was "A11:A109," then Excel first checks that C1 is greater than 100 and then if A11 equals "Doe, John." Excel then proceeds to check C2 and A12, and so on.
Add more ranges and criteria until all your conditions are met. Unlike the CountIf function's restriction of a single comparison, the CountIfs function supports up to 127 pairs of ranges and criteria.
Tips & Warnings
- Both functions support wildcard characters, such as a question mark or asterisk, to match a single character or multiple characters, respectively. For example, a criteria of "r?n" matches "run" or "ran," but not "rain." A criteria of "r*n" matches "rn," "run," "ran" or "rain." If you need to match an actual question mark or asterisk, put a tilde in front of the mark, such as "are you sure~?" to match the text followed only by a question mark.