COUNTIFS Function in Excel – Count cells with multiple conditions in Excel

COUNTIFS function in Excel returns the count of cells that meet one or more conditions. COUNTIFS Function in Excel can be used to count cells with dates, numbers, and text that match one or more conditions. The COUNTIFS function in Excel supports logical operators (>,<,<>,=) and wildcards (*,?) for pattern matching

Wild cards in COUNTIFS Function

  • ? –    matches any single character
  • * –    matches any sequence of characters

Example:

The condition “A?e” will match all cells containing a text string beginning with “A” and ending in “e” and only one character in between i.e. A_e.

The condition “A*e” will match all cells containing a text string beginning with “A” and ending in “e” with multiple character(n) in between i.e. A_______e.

 

Syntax of COUNTIFS Function in Excel:

COUNTIFS (range1, criteria1, [range2], [criteria2], …)
  • range1 – The First range of cells to be counted.
  • criteria1 – The criteria or condition on which cells should be counted based on range1
  • range2 – [optional] The Second range of cells to be counted.
  • criteria1 – [optional]The criteria or condition on which cells should be counted based on range2

 

Example of COUNTIFS Function in Excel

Formula

COUNTIFS FUNCTION IN EXCEL 1

  • First two examples states the use of logical operation in COUNTIFS function with two conditions.
  • Last two examples states the use of wildcards in COUNTIFS function with two conditions.

So the result will be like

Result

COUNTIFS FUNCTION IN EXCEL 2

previous COUNTIFS Function in Excel                                                                                                                 next COUNTIFS Function in Excel