|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

COUNTIFS

COUNTIFS

  • Last updated on

Description

Use this function to count the number of cells that meet multiple criteria. Can be used with CHILDREFS.

COUNTIFS is useful for counting the number of cells that match multiple conditions across one or more ranges.

Syntax

COUNTIFS(criteria_range_1, criterion_1,[…,criteria_range_126, criteria_126])

COUNTIFS supports up to 127 range/criteria pairs.

Inputs

This function accepts the following arguments:

Name Required Description Valid input
cell_range_1 Yes The first range in which to evaluate the associated criteria. A range of cells in any worksheet.
criterion_1 Yes The criteria to use on criteria_range1. A number, expression, cell reference, or text that defines the condition that the cells in cell_range_1 must meet.
cell_range_n No Additional range(s) to be evaluated. A range of cells in any worksheet. 
criterion_n No The criteria to use on the associated range. A number, expression, cell reference, or text that defines the condition that the cells in this cell_range must meet.

 

Examples

Sample data

  A B C D E
1 Item Value Price Margin Discount
2 T-shirt $23.00 $30.00 23.33% 10%
3 Candle $1.20 $6.00 80.00% 5%
4 Pillow        
5 Blanket $12.25 $36.00 65.97% 20%
6 Rug $18.60 $45.00 58.67% 8%
7          
8          
9          
10          

Sample formulas

Use case Formula Explanation and Result
Count the number of cells in a given range a specified value appears. =COUNTIFS(A1:A4, "Apples")

This formula works in the following manner:

  • COUNTIFS(: This is the function itself.
  • A1:A4: The first criteria_range.
  • "Apples": The criteria applied to the first range. It counts cells containing "Apples".

For this data set, the formula returns 2.
Count the number of cells in a given range that
1) has a specified value and
2) has a specified value in a second range.		 =COUNTIFS(A1:A4, "Apples", B1:B4, ">15")

This formula works in the following manner:

  • COUNTIFS(: This is the function itself.
  • A1:A4: The first criteria_range.
  • "Apples": The first criteria, applied to A1:A4.
  • B1:B4: The second criteria_range.
  • ">15": The second criteria, applied to B1:B4. It counts cells greater than 15.
  • The function counts rows where *both* criteria are met simultaneously.

For this data set, the formula returns 1.
Count the number of cells in a given range that are greater than a specified value. =COUNTIFS(B1:B4, ">20")

This formula works in the following manner:

  • COUNTIFS(: This is the function itself.
  • B1:B4: The criteria_range.
  • ">20": The criteria. It counts cells with a value greater than 20.

For this data set, the formula returns 1.
Count the number of cells in a given column that
1) has a specified value and
2) has a specified value in a second column.		 =COUNTIFS(A:A, "Apples", C:C, "Marie")

This formula works in the following manner:

  • COUNTIFS(: This is the function itself.
  • A:A: The first criteria_range (entire column A).
  • "Apples": The first criteria, applied to column A.
  • C:C: The second criteria_range (entire column C).
  • "Marie": The second criteria, applied to column C.
  • The function counts rows where column A contains "Apples" AND column C contains "Marie".

For this data set, the formula returns 1.
Count the number of cells in a given column that
1) is a specified value and
2) has a specified value in a second column.		 =COUNTIFS(D1:D6,">="&DATE(2023,1,1),D1:D6,"<"&DATE(2023,2,1))

This formula works in the following manner:

  • COUNTIFS(: This is the function itself.
  • D1:D6: The first criteria_range (dates).
  • ">="&DATE(2023,1,1): The first criteria. It checks if the date is greater than or equal to January 1, 2023.
    The DATE function creates the date value, and the ampersand (&) concatenates it with the operator.
  • D1:D6: The second criteria_range (same as the first).
  • "<"&DATE(2023,2,1): The second criteria. It checks if the date is less than February 1, 2023.
  • The function counts rows where the date in column D falls within the month of January 2023.

For this data set the formula returns: 3.

 

Notes

  • COUNTIFS allows you to apply multiple criteria across multiple ranges.
  • All criteria must be met for a cell to be counted.
  • The ranges must be the same size, otherwise COUNTIFS will return an error.
  • Criteria can include logical operators such as >, <, >=, <=, <>, and wildcards (*, ?).

 

Tips

  • Use COUNTIFS to analyze data based on multiple criteria, such as sales performance by region and product.
  • Combine COUNTIFS with other functions such as SUMIFS to create more complex formulas.
  • Use COUNTIF if you only have a single criterion.
  • Make sure your ranges are the same size to avoid errors.

 

Related functions

COUNT
COUNTA
COUNTBLANK
COUNTIF
COUNTIFS
ISBLANK
ISERROR
ISNA
ISNUMBER
VALUE

Was this article helpful?
0 out of 0 found this helpful
Description Use this function to count the number of cells that meet multiple criteria. Can be used with CHILDREFS. C...