Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

COUNTIFS

Count reference cells based on multiple criteria in Excel
Counts the number of cells that meet multiple criteria

Rational

Count reference cells based on multiple criteria.

Description

The Excel COUNTIFS function returns a count of numbers based on one or multiple given criteria. COUNTIFS can be used to count values when selected cells meet criteria based on dates, numbers, and text.

Syntax

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

Parameters

range1 – The first range to evaluate containing criteria1
criteria1 – The criteria to apply for addition in range1
range2 [optional]The second range to evaluate containing criteria2
criteria2 [optional] – The criteria to apply for addition in range2

Return Value

Count of values based on multiple criteria

Key Notes

  • The COUNTIFS function is a plural variation of the COUNTIF function as it accepts multiple arguments to count one or multiple ranges or array of numbers.
  • The function can handle between 1 to 127 range or criterion pairs.
  • For text criteria that include logical operators (>,<,<>,=) or wildcards symbols (*,?) for partial matches must be enclosed in double quotation marks (“) like “<30”.
  • The user can find a literal question mark or asterisk by using a tilde (~) symbol before a question mark or asterisk (i.e., ~?, ~*). A criteria a*a will match with all cells containing a string beginning with “a” and ending in “a” like a text “abide” or “abate”.

Keep Reading Similar Functions:

Count reference cells based on criteria in Excel

COUNTIF

The COUNTIF function returns a count of number based on given criteria like =COUNTIF(A1:A5,”>5″) that count numbers in the list >5 and returns 3.

Read More »
Sum range of cells based on multiple criteria in Excel

SUMIFS

The SUMIFS function returns an addition of numerical as per multiple criteria based on the match of dates, numbers, and text against the reference.

Read More »
Counts cells with only Numbers in Excel

COUNT

The COUNT function count of numbers within the reference list of arguments ignoring text & blank cells, =COUNT(A1:A10) will return 5 (only numeric).

Read More »
Find the average of a cell range based on multiple criteria in Excel

AVERAGEIFS

The AVERAGEIFS function returns an average number based on one or multiple given criteria, sum adjacent cells that meet given conditions.

Read More »
Counts cells that are not blank in Excel

COUNTA

The COUNTA function count of numbers and text within the reference list of arguments ignoring blank cells, =COUNTA(A1:A10) will return 7 (disregard blank).

Read More »