How to use the COUNTIFS Formula in Excel

The COUNTIFS formula is a formula used to apply multiple criteria to a range of cells to count the number of times it meets all of those criteria. The formula applies criteria to cells across multiple ranges and counts the number of times all criteria are met.

COUNTIFS Formula Explained:

=COUNTIFS (criteria_range, criteria1, [criteria_range2, criteria2], …)

  • The COUNTIFS formula is comprised of 3 items and if applicable you can add more for criteria range, criteria, and so forth.

Example Using the COUNTIFS Formula:

The General Manager of a second-hand car dealership wants to know how many cars were sold for certain car models at specific locations during the year.

The Financial Analyst generates a Sales Journal (below) containing the raw data of car sales with product code, product descriptions, and dealer branch location.

The Financial Analyst can use the COUNTIFS formula to summarize the number of car sales by branch and product description for the General Manager. See the right column
of the below table for the summary of this data which was obtained using the COUNTIFS formula on the sales journal.

*Note this table is only a sample of an example with 5 data entries while the rest of the data has been cut out for display purposes.

Here,

  1. Criteria_range1 formula was used for determining the data range of a specific branch location.

The COUNTIFS formula is powerful in the sense that it has the flexibility of allowing for multiple conditions to be set. In this example, we only set two criteria but we could have applied further criteria and use the date column or the product code column. This was not necessary for this example because we only wanted to report back to the general manager on the branch location and product description.

I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!