COUNTIFS

Formulas / COUNTIFS
Sums up the cells matching several criteria.
==COUNTIFS(range1, criteria1, [range2], [criteria2], ...)
  • range1 - required; the first range to evaluate
  • criteria1 - required; the criteria to use on range1
  • range2 - [OPTIONAL] the second range to evaluate
  • criteria2 - [OPTIONAL] the criteria to use on range2

Examples

    The example =COUNTIFS(C5:C14,"red",F5:F14,">20") counts the number of records in C5:C14 that have a "red" criterion and a ">20" criterion. This would return the number of objects that are both red and greater than 20 in the specified range.

    COUNTIFS can also be used to count the number of records that meet two criteria, such as =COUNTIFS(A5:A14,">100",B5:B14,"<200"). This would return the number of records in A5:A14 that are greater than 100 and less than 200 in B5:B14.

    The COUNTIFS function can also be used to count the number of records that meet multiple criteria, such as =COUNTIFS(A5:A14,">100",B5:B14,"<200",C5:C14,"= green"). This would return the number of records in A5:A14 that are greater than 100, less than 200, and equal to "green" in C5:C14.

    The COUNTIFS function can also be used to count the number of records that meet text criteria, such as =COUNTIFS(A5:A14,"apple",B5:B14,">2"). This would return the number of records in A5:A14 that contain the text "apple" and are greater than 2 in B5:B14.


Summary

The COUNTIFS function is a powerful tool that allows users to count how many times a set of criteria is met. It takes up to 127 range/criteria pairs and each range must have the same number of rows and columns as the COUNTIFS arguments.

  • The COUNTIFS function supports partial matching with wildcards, counting cells that have a date, number, or text, and requires that all conditions be TRUE.
  • COUNTIFS is a common Sourcetable function that can be used to count cells in a range that meet one or more criteria.

Frequently Asked Questions

What is the COUNTIFS function?
The COUNTIFS function allows you to apply criteria to cells across multiple ranges and count the number of times all criteria are met.
How many range/criteria pairs can COUNTIFS take?
COUNTIFS can take up to 127 range/criteria pairs.
Do the additional ranges have to be adjacent?
No, the additional ranges do not have to be adjacent.
Do the additional ranges need to have the same number of rows and columns as the criteria argument?
Yes, each additional range must have the same number of rows and columns as the criteria argument.
Can COUNTIFS use wildcard characters in its criteria?
Yes, COUNTIFS can use wildcard characters in its criteria.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV