COUNTIFS Function / Formula in Excel

1
619

COUNIFS Function

The Excel COUNTIFS function works for give the count of cells that meet one or more criteria. COUNTIFS can be used to count cells that contain dates, numbers, and text, with logical operators (>,<,<>,=).

Syntax 

=COUNTIFS(range1, criteria1, [range2], [criteria2], …) and so on, which can check things like

Usage

The COUNTIFS function is a common, widely used function in Excel, and can be used to count cells that contain dates, numbers, and text.

Example COUNTIFS function

Find the greater than sale of Product type:

The conditions are that the type is “Soap” and Sale is >9000.

Example COUNTIFS function, step by step:

  1. Select the cell H2
  2. Type =COUNTIFS
  3. Click the COUNTIFS command
  4. Select Specify the range for the type A2:B13 (the Product)
  5. Select Specify the criteria (the cell F2, which has the value “SOAP”)
  6. Select Specify the range for the second condition B2:B13 (the Sale values)
  7. Select Specify the SING “>” & criteria (the cell G2, which has the value “9000”)
  8. Hit enter

Now, we can see the total Count number of product type and sale is greater than 9000 on H column.

In COUNTIFS function we can use following function also

  1. Cells equal to “text”

Formula : COUNTIFS(C5:C14,”F2″,D5:D14,”Text”)

  1. Cells greater than 20

Formula : COUNTIFS(C5:C14,”F2″,D5:D14,”>20″)

In general, text values need to be enclosed in double quotes, and numbers do not.

=COUNTIFS(A1:A10,9000) “ count equal to 9000”

=COUNTIFS(A1:A10,”>500″) “count greater than 500”

=COUNTIFS(A1:A10,”Soap”) “count equal to “Soap”

Not equal to

=COUNTIFS(A1:A10,”<>red”) “not “red”

Blank cells

=COUNTIFS(A1:A10,”<>”) “not blank”

=COUNTIFS(A1:A10,””) “blank”

Wildcards

Note : Wild card means if text is between an word or in start then this function will use

Now result will come as follows for wildcard and countifs formula

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here