Learn how to use the Excel functions COUNTIFS and COUNTIF

Excel Functions COUNTIFS and COUNTIF count cells with values that meet specified criteria. These criteria can be based on dates, numbers, or text. Criteria based on dates and text must be enclosed with double quotes (i.e. “01/01/2019”). On the other hand, a number will require double quote only if it has operators (i.e “>100”). Otherwise double quote is not required. Both functions support logical operators such as “Greater Than” (>), “Less Than” (<), “Not Equal to” (<>) , “Equal” (=)  and wildcards (*,?) for partial matching. Although both functions support calculations based on criteria, Excel COUNTIF count cells with values based on a single criterion while COUNTIFS analyze based on multiples criteria.

SYNTAX

  • COUNTIF

=COUNTIF (range, criteria)

  • range - The range of cells to count and where the criteria are applied against.

  • criteria – Defines the criteria that tell the function which cell should be included in the count.

  • COUNTIFS

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

      • criteria_range1 - The first range of cells to count where criteria1 is applied against.

      • criteria1 - The criteria to use on criteria_range1.

      • [criteria_range2] - The second range of cells to count and where [criteria2] is applied against. This argument is optional.

      • [criteria2] - The criteria to use on [criteria_range2]. This argument is optional. Note that range and criteria are supplied in pairs and Excel COUNTIFS function allows up to 127 pairs of range and criteria.

BASIC APPLICATION – COUNTIF

Figure A. COUNTIF Function

Suppose you have a data that shows Server Names, its location in Region, and Status as illustrated in Figure A and you want to count how many servers are in Europe.

Instructions:

  1. Following the syntax =COUNTIF (range, criteria), type below formula into cell D22 where, D5:D16 is the range of cells to count and where the set criteria of “Europe” is placed. Alternatively, you can highlight ranges D5 to D16 in excel while typing the formula and you will get the same result.

    =COUNTIF (D5:D16, “Europe”)

  2. Once done entering the formula, press ENTER.

  3. To check if you have highlighted the correct range and criteria, go to cell D22 (where your formula or function is located) and press F2. This will reveal your formula including the range you have used. In this case, range is highlighted in blue.

BASIC APPLICATION – COUNTIFS

Figure B. COUNTIFS Function

On this second example, you have the same data presented in COUNTIF function. However, in this case, you require more than one criterion where you want to find how many servers are in “Europe” and is “Online”.

Instructions:

  1. Following the syntax =COUNTIFS (criteria_range1, criteria1, [criteria_range2], [criteria2], ...), type below formula into cell D22 where:

      • range2] where the criteria2 is placed

      • “Online” is the criteria2 Then press ENTER. Alternatively, you can highlight ranges D5 to D16, and E5 to E16 in excel while typing the formula and you will get the same result.
        =COUNTIFS (D5:D16,"Europe", E5:E16,"Online")

  2. Repeat the same method as indicated in COUNTIF function to verify if you have highlighted the correct ranges.



If you would like to read more about COUNTIF and COUNTIFS, check out the Microsoft guide in these links:

https://support.office.com/en-us/article/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34

https://support.office.com/en-us/article/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842
 

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit