How to Use the COUNTIFS Function in Excel

The COUNTIFS Function

This tutorial shows how to use the COUNTIFS function to count records that meet one or more conditions in Excel. In the example from the video, the function is used to count HVAC jobs by job type, by technician, and then by both technician and job type together.

1. Start by counting one category at a time

• Begin with a list of job types such as Emergency, Install Estimate, Maintenance, and Repair
• Use COUNTIFS to count how many rows match each job type
• Select the Job Type column as your criteria range
• Reference the cell that contains the job type label you want to count
• Fill the formula down to return a count for each category in the list

2. Count jobs by technician

• Create a second list with technician names
• Use COUNTIFS again, this time pointing to the Technician column
• Reference the cell containing each technician’s name
• Drag or double-click to fill the formula down the list
• This gives you a quick count of how many jobs were assigned to each technician

3. Combine two conditions in one count

• Build a small matrix where one dimension is technician and the other is job type
• Use COUNTIFS with two criteria:
  • one for the Technician column
  • one for the Job Type column
• This lets you count combinations such as:
  • Emergency calls handled by Anthony
  • Maintenance jobs completed by Mike Torres
  • Repairs assigned to another technician

4. Lock your references before copying across the table

• When building the technician-by-job-type table, use absolute referencing so the formula copies correctly across rows and columns
• Use F4 to cycle through reference locking options
• Lock the row or column depending on whether the label should stay fixed when the formula is copied
• This is what allows one formula to be pasted across the full matrix without breaking

5. Copy the formula across the matrix

• Once the references are locked correctly, copy the formula
• Paste it across the full table
• Excel will now return the count for each technician and job type combination
• This creates a more detailed breakdown than the simple single-condition counts

6. Check your results with totals

• Add totals to each row or column to verify the counts
• Use SUM or the shortcut Alt + = to total the results
• Compare those totals to your earlier one-condition counts
• The totals should match, which confirms your COUNTIFS setup is working correctly

7. Use COUNTIFS as your default counting function

• In the video, COUNTIFS is used even when there is only one condition
• That is because COUNTIFS can handle both simple and more advanced counting scenarios
• Instead of switching between COUNTIF and COUNTIFS, the workflow stays consistent with one flexible function

8. Expand the same logic to other HVAC tracking needs

• After learning this setup, you can use COUNTIFS for many other operational counts, such as:
  • emergency calls by technician
  • maintenance visits by month
  • service calls by region and technician
  • install estimates by lead source and rep

This same structure works any time you need to count rows based on multiple filters at once.