Steps to count cells in Excel with pre defined criteria

To count number of cells in excel is easy with COUNT function. When there is a need to count cells with predefined condition, then we use another function. COUNTIF function in Excel is used to count cells with pre defined criteria. In other words, COUNTIF helps condition based counting of cells. We will cover steps to count cells in Excel with pre defined criteria.

John has his company’s customer information available with him. He want to know the count by male and female employees. Below is the data that John has:

The key steps to use COUNTIF function to reach the solution:

• Step 1: First parameters in COUNTIF is Range. Range will be the column where a criteria has to be checked. In the customers data column G has Gender information. So the same cells G4:G29 will be used.

• Step 3: Then fixing the range cell is important. This will help in easy replication for Male gender.

• Step 4: Criteria will be equal to the cell K5, as we want to count number of customers for Female gender. This will enable criteria check in each cell of column G. Press enter and we will have count of Female employees ready.

• Step 4: Copy the formula from L5 and paste to L6 to get count of male employees as well. In conclusion, John is able to solve the problem and get the desired results.

Example 2, How to use COUNTIF in Excel

Further John is looking to understand client of customers by country. He can again use COUNTIF function and get the distribution as required.

The key steps to use COUNTIF function to reach the second solution:

• Step 1: Firstly Range will have the country column from customer data table.

• Step 2: Also fix the range cell to ensure error free copying in below cells.

• Step 3: The criteria will contain Country value cell i.e. K5. Press Enter and the required count of customer will be available now.

• Step 4: Copy the formula from L5 to L6:L8 to get count of customers across all the countries. Similarly to the first problem, John achieved the results here as well.

Thus, COUNTIF function can be a very handy function in Microsoft Excel. In summary, putting the parameters correctly is important to get the results.

To get top certifications in Microsoft Excel and build your resume visit here. Additionally, you can read books listed here to build strong knowledge around Microsoft Excel.

Visit our video lesson to learn more:

Please drop a message to receive downloadable Excel file for COUNTIF function practice: