COUNTIFS Function in Excel


Home >> How to Use Excel Tutorial >> Excel Formulas List >> COUNTIFS Function in Excel



In this tutorial, you will understand about how to use excel COUNTIFS formula. COUNTIFS function in excel can be used to calculate data amount in a cell table with more than one criteria.


Why We Need to Learn About Excel COUNTIFS Formula?


When we have data in our spreadsheet tool, sometimes we want to know how many of them have some condition that we determine so we can use it as a basis for processing or decision making. The condition used for the evaluation process for the data sometimes can be more than one.

For example of this case, if we want to make decision about how much bonus will be given to the star employees in company, then we need to know how many star employees we have so we can estimate the budget allocation and how much bonus should be given to each of the employee. In determining it, we can have some criteria to decide who are the star employees like the performance score based on the supervisor evaluation, how many days does the employee come late in a year, and other criteria. If the company have many employees, then it will be difficult to do the people counting manually even if we have enough details for each of the company employee.

For this purpose, there is COUNTIFS function in excel which can be used to make the process done easier. Excel COUNTIFS formula itself can be used to calculate how many data we have with more than one criteria for the data that we determine before.


What is COUNTIFS Function in Excel?


COUNTIFS function in excel is the formula provided to calculate the amount of data with some criteria. This excel COUNTIFS formula can be used by inputting cell range in the form of row or column from the data evaluated and the condition as the basis for evaluation process of that row or column. Row / column and the evaluation condition correlated must be inputted in sequence. If you want to evaluate with only one condition, then you can use COUNTIF formula, although excel COUNTIFS formula can also be used to evaluate data with one condition.

A bit explanation about the inputs in the formula as follows:


=COUNTIFS(criteria_range1, criteria1, …)


Note:
  • criteria_range1 = row/column cell range where the evaluation based on the first criteria is done
  • criteria1 = your first criteria
  • … = row/column cell ranges where the evaluation for the next criterias is done and also the next criterias



How to Use Excel COUNTIFS Formula?


The next part will explain about how to use COUNTIFS function in excel. Evaluation process of this formula will be done on per row/column with each condition will be looked at per column/row on your table based on your inputs for this excel COUNTIFS formula.


Using COUNTIFS Function in Excel

  1. Type equal sign ( = ) in the cell where you want to put the result in

    COUNTIFS Function in Excel - Screenshot of Step 1

  2. Type COUNTIFS (can be with large and small letters) and open bracket sign after =

    COUNTIFS Function in Excel - Screenshot of Step 2

  3. Drag cursor on the cell range in a column/row where your data is and type comma sign ( , )

    COUNTIFS Function in Excel - Screenshot of Step 3

  4. Type your data criteria. The criteria must be inside quote sign ( “” ) if it is in form of text or more than/less than condition for the numbers

    COUNTIFS Function in Excel - Screenshot of Step 4

  5. Type comma sign, drag cursor on the cell range for the next column/row of data place, type comma sign again, and type data criteria for this cell range if there are still cell range and criteria which are needed to be inputted. The additional cell range must be inline vertically/horizontally with the previous input and should have the same number of cells

    COUNTIFS Function in Excel - Screenshot of Step 5

  6. Type close bracket sign after all have been inputted.

    COUNTIFS Function in Excel - Screenshot of Step 6

  7. Press Enter
  8. The process is done!

    COUNTIFS Function in Excel - Screenshot of Step 8



Exercise


After you have learned how to use excel COUNTIFS formula from the explanation above, you can practice your understanding through this exercise!

Download the file from the following link and answer questions below. Please download the answer file if you have done practicing your understanding to answer the questions and sure about the results!

Link to download file for your understanding about COUNTIFS function in excel practice:
Download here

Questions

  1. How many rows with the letter A in the first and second column?
  2. How many rows with the letter A in the second and third column?
  3. How many rows with the letter A in the first column, B in the second column, and C in the third column?

Link to download answer file:
Download here


Additional Notes

  • If you want, cell range input for COUNTIFS function in excel can also be written manually separated for the most top left cell and most bottom right cell by two dots sign ( : ) (Writing example: A1:A25)
  • There are variants of excel functions to calculate amount of data like COUNT and COUNTA. The tutorial for these formulas can be found in other parts of this Compute Expert blog!




Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on


CONTACT US PRIVACY POLICY TERMS AND CONDITION AFFILIATE DISCLOSURE @Compute Expert