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.
• Criteria_range1 (required) – The first range in which to evaluate the criteria set in criteria.
• Criteria1 (required) – The criteria in the form of a number, expression, cell reference, or text that define which cells are to be counted within the criteria1 range.
• Criteria_range2, criteria2, … (optional) – Additional ranges and their related criteria. You can enter up to 127 range/criteria pairs.

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.
2. Criteria1 formula was used for determining a specific branch location.
3. Criteria_range2 formula was used for determining the data range of what type of product description should be counted.
4. Criteria2 formula was used for filtering only the specified product description required.

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!