For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

Statistical

COUNTIFS Function

Counts the number of cells that meet multiple criteria


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

Syntax

COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)

  • criteria_range1  Required. The first range in which to evaluate the associated criteria.
  • criteria1  Required. The criteria in the form of a number, expression, cell reference, or text that defines which cells will be counted.
  • criteria_range2, criteria2, ....  Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

Applies To

Excel 2007 and above

Examples

You can download this example workbook here, along with all other example workbooks I've completed for this Excel help area.

Example 1 for Excel's COUNTIFS function

Only one set of cells satisfies both criteria

Example 2 for Excel's COUNTIFS function

Because all rows meet these criteria, the formula returns the count of all three.

Example 3 for Excel's COUNTIFS function

The Sales range has two values less than thirty.

Example 4 for Excel's COUNTIFS function

Only the final number has a value greater than 10 and satisfies the second criteria.

Example 5 for Excel's COUNTIFS function

COUNTIFS can count multiple rows and columns, if their shape is identical, as these are.

Example 6 for Excel's COUNTIFS function

COUNTIFS can NOT count multiple rows and columns if their shape is NOT identical, as shown here.

Other Help

 

Charley's SwipeFile charts


Free Excel Dashboards