How to use the Excel COUNTIFS function | Exceljet

The COUNTIFS officiate in Excel counts the count of cells in a stove that match one supplied criteria. Unlike the older COUNTIF officiate, COUNTIFS can apply more than one condition at the same time. Conditions are supplied with range/criteria pairs, and only the first couple is required. For each extra stipulate, you must supply another range/criteria pair. Up to 127 range/criteria pairs are allowed .
Criteria can include legitimate operators ( >, ,= ) and wildcards ( *, ? ) for partial derivative match. Criteria can besides be based on a value from another cell, as explained below .
COUNTIFS is in a group of eight functions in Excel that split coherent criteria into two parts ( crop + criteria ). As a result, the syntax used to construct criteria is different, and COUNTIFS requires a cell range for crop arguments, you ca n’t use an array .

Basic example

With the exemplar shown, COUNTIFS can be used to count records using 2 criteria as follows :

 = COUNTIFS (C5:C14, `` loss '' ,D5:D14, `` texas '' )  // bolshevik and TX
 = COUNTIFS (C5:C14, `` red '' ,F5:F14, `` > 20 '' )  // crimson and > 20

Notice the COUNTIFS function is not case-sensitive .

Double quotes (“”) in criteria

In general, textbook values need to be enclosed in double quotes, and numbers do not. however, when a coherent hustler is included with a phone number, the number and operator must be enclosed in quotes as shown below :

 = COUNTIFS (A1:A10, 100 )  // count peer to 100
 = COUNTIFS (A1:A10, `` > 50 '' )  // count greater than 50
 = COUNTIFS (A1:A10, `` jim '' )  // count peer to `` jim ''

note : showing one circumstance only for chasteness. extra conditions must follow the same rules .

Value from another cell

When using a measure from another cell in a stipulate, the cell citation must be concatenated to an hustler when used. In the case below, COUNTIFS will count the values in A1 : A10 that are less than the value in cell B1. Notice the less than hustler ( which is text ) is enclosed in quotes, but the cell citation is not :

 = COUNTIFS (A1:A10, `` < `` &B1 )  // count cells less than B1

note : COUNTIFS is one of respective functions that split conditions into two parts : roll + criteria. This causes some inconsistencies with respect to other formulas and functions .

Not equal to

To construct `` not equal to '' criteria, use the `` < > '' operator surrounded by double quotes ( `` '' ). For exemplar, the formula below will count cells not peer to `` red '' in the range A1 : A10 :

 = COUNTIFS (A1:A10, `` < > crimson '' )  // not `` red '' 

Blank cells

COUNTIFS can count cells that are blank or not lacuna. The recipe below consider blank and not blank cells in the compass A1 : A10 :

 = COUNTIFS (A1:A10, `` < > '' )  // not blank
 = COUNTIFS (A1:A10, `` `` )  // blank

Dates

The easiest way to use COUNTIFS with dates is to refer to a valid date in another cell with a cell reference. For exercise, to count cells in A1 : A10 that contain a go steady greater than a date in B1, you can use a rule like this :

 = COUNTIFS (A1:A10,  `` > '' &B1 )  // count dates greater than A1

Notice we concatenate the `` > '' operator to the date in B1, but and are no quotes around the cell reference .
The safest means to hardcode a go steady into COUNTIFS is with the DATE serve. This guarantees Excel will understand the date. To count cells in A1 : A10 that contain a go steady less than September 1, 2020, you can use :

 = COUNTIFS (A1:A10, `` < `` & date ( 2020, 9, 1 ) )  // dates less than 1-Sep-2020

Wildcards

The wildcard characters question check ( ? ), star ( * ), or tilde ( ~ ) can be used in criteria. A question notice ( ? ) matches any one character, and an asterisk ( * ) matches zero or more characters of any kind. For exercise, to count cells in A1 : A5 that contain the textbook `` apple '' anywhere, you can use a convention like this :

 = COUNTIFS (A1:A5, `` *apple* '' )  // consider cells that contain `` apple ''

The tilde ( ~ ) is an safety valve fictional character to allow you to find literal wildcards. For case, to count a literal question bell ringer ( ? ), asterisk ( * ), or tilde ( ~ ), add a tilde in presence of the wildcard ( i.e. ~ ?, ~*, ~~ ) .

OR logic

The COUNTIFS serve is designed to apply multiple criteria, but conditions are applied with AND logic. This means if you try to count cells that contain `` loss '' or `` gloomy '' in the same range, the result will be zero ( 0 ). however, to count cells with OR logic, you can use an array constant and the SUM function like this :

 = sum ( COUNTIFS (range, { `` crimson '', `` blue '' }  ) )  // red or blue

The formula above will count cells in scope that check `` crimson '' or `` bluing ''. Briefly, COUNTIFS returns two counts in an align ( one for `` red '' and one for `` blue '' ) and the SUM function returns the sum as a final leave. For more information, see this exercise .

Notes

  • Multiple conditions are applied with AND logic, i.e. condition 1 AND condition 2, etc.
  • Each additional range must have the same number of rows and columns as range1, but ranges do not need to be adjacent. If you supply ranges that don't match, you'll get a #VALUE error.
  • Non-numeric criteria needs to be enclosed in double quotes but numeric criteria does not. For example: 100, "100", ">32", "jim", or A1 (where A1 contains a number).
  • The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters.
  • To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).

Related Posts

Leave a Reply

Your email address will not be published.