Count cells with multiple criteria in Excel 2007

In an earlier post I demonstrated How to count cells with multiple criteria in Excel 2003. I used an Array Formula together with the SUM function.

Fortunately enough in Excel 20007 we do not need that workaround. Now there is a direct function that count cells with multiple criteria: the COUNTIFS function.

Syntax

COUNTIFS(range1, criteria1,range2, criteria2…) … up to 127 range/criteria can be specified.

In the example below we want to count how often “John” appeared in the list in quarter 2 (Q2), with total sales that exceed 1000. i.e. we have three criteria:

  1. “John” in column B.
  2. “Q2″ in column C.
  3. “>1000″ in column D.

We will use the following formula for this purpose:

=COUNTIFS(B2:B17;”=John”;C2:C17;”=Q2″;D2:D17;”>1000″)

This formula will give us a result of 2 as you can see.

xl7countifs

Comments

  1. Maruf Ahamed says:

    Suppose you have a database.
    Number of Questions
    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
    Number of respondents Garibe Newaz 1 4 4 3 4 3 3 4 3 4 5 5 4 5 4 4 3 3 4 4 4 4 4
    2 4 4 3 5 4 4 2 3 3 3 4 4 5 5 4 3 3 4 5 4 3 4
    3 4 5 4 5 5 4 4 3 4 4 4 4 5 4 4 3 3 3 5 4 5 5
    4 5 4 4 5 4 3 3 3 4 4 4 4 5 4 4 4 4 5 4 5 5 5
    5 5 4 3 4 4 4 4 3 4 4 5 4 5 4 4 3 4 3 4 4 3 4
    6 4 5 4 4 5 4 5 3 4 2 3 3 5 4 4 2 3 3 5 4 4 5
    7 5 4 3 4 3 3 3 2 4 2 4 3 5 3 2 1 3 4 3 2 4 4
    8 4 4 4 3 3 3 4 2 3 2 4 4 5 4 3 2 3 3 5 2 4 4
    9 4 4 3 4 4 3 4 3 2 2 4 3 5 3 4 2 1 3 4 5 4 5
    10 4 4 3 2 4 4 4 2 4 4 4 3 5 4 4 2 2 4 5 4 3 3
    Islami Bank Hospital 11 4 4 3 5 5 4 4 3 2 4 4 4 4 3 4 2 4 4 3 2 4 2
    12 4 3 4 4 4 4 5 2 4 5 4 4 5 5 5 4 3 4 4 3 3 4
    13 5 5 3 4 4 4 2 2 4 5 4 5 3 5 2 3 4 2 5 3 3 4
    14 4 5 3 4 4 5 4 3 4 4 5 4 5 5 4 3 4 4 4 3 4 4
    15 4 4 3 4 4 3 4 3 4 2 3 3 5 4 4 2 4 3 3 3 4 3
    16 4 4 3 4 4 3 4 3 4 2 3 3 5 3 3 2 4 3 3 3 4 3
    17 2 4 4 4 3 3 4 3 4 3 2 3 5 4 4 3 3 4 4 4 3 4
    18 4 4 3 4 4 4 3 3 2 3 3 4 2 4 4 4 3 3 5 4 3 4
    19 5 4 3 4 3 4 4 3 4 4 3 4 5 5 4 2 3 3 2 4 2 3
    20 5 4 3 3 2 3 4 2 4 4 3 2 5 4 5 3 3 4 3 4 3 4
    City Imaging Center 21 4 4 3 4 4 3 4 4 4 4 4 4 5 4 5 4 3 4 3 4 5 4
    22 5 4 3 4 4 3 4 3 4 4 4 4 3 4 4 4 3 3 4 3 4 4
    23 4 4 3 3 4 3 3 4 3 3 4 2 3 4 3 3 3 4 4 3 4 3
    24 5 4 4 5 4 5 5 5 4 4 4 4 5 5 4 3 4 5 3 4 5 5
    25 4 1 3 3 3 3 4 3 4 2 4 3 5 4 4 4 3 4 3 4 4 3
    26 4 4 3 4 4 3 4 4 4 4 4 4 5 4 5 4 3 4 3 4 5 4
    27 5 4 3 4 4 3 4 3 4 4 4 4 3 4 4 4 3 3 4 3 4 4
    28 4 4 3 3 4 3 3 4 3 3 4 2 3 4 3 3 3 4 4 3 4 3
    29 5 4 4 5 4 5 5 5 4 4 4 4 5 5 4 3 4 5 3 4 5 5
    30 4 1 3 3 3 3 4 3 4 2 4 3 5 4 4 4 3 4 3 4 4 3
    Raisa Clinic & Diagnostic Center 31 5 5 5 5 5 5 5 4 5 4 5 5 3 5 4 5 5 5 5 5 4 5
    32 5 4 5 5 5 5 5 4 5 4 5 5 5 5 4 4 5 5 5 5 5 5
    33 4 4 4 5 5 5 4 5 4 4 5 5 5 5 4 4 5 5 5 5 5 5
    34 4 5 4 5 3 5 5 3 5 4 5 5 5 5 5 5 5 5 5 5 5 5
    35 4 5 3 5 5 5 5 3 5 5 5 5 5 5 4 4 5 5 5 5 5 5
    36 5 4 5 4 4 5 5 3 5 5 5 5 5 5 4 4 5 4 5 4 4 4
    37 4 4 5 5 5 5 5 4 5 4 5 4 5 5 5 5 5 5 5 5 5 5
    38 5 5 5 5 5 4 5 4 5 4 5 5 3 5 4 4 5 5 5 5 4 5
    39 4 4 3 4 4 5 3 4 4 4 5 5 5 3 4 3 5 4 5 4 4 4
    40 4 5 4 5 5 5 4 5 4 4 4 5 5 5 5 4 5 5 5 5 5 5
    Padma Diagnostic Center 41 4 4 3 4 3 4 4 3 4 4 4 3 4 4 4 4 4 3 4 3 5 4
    42 4 4 3 5 3 4 5 4 5 5 5 4 3 5 4 4 3 3 3 3 4 3
    43 2 4 2 4 4 4 4 3 4 3 4 5 5 5 3 1 3 3 4 1 3 3
    44 4 4 2 4 3 4 5 3 5 4 5 5 5 5 2 4 5 5 4 1 5 4
    45 2 2 4 3 5 4 5 4 5 3 4 5 5 4 4 3 3 3 4 3 5 5
    46 4 4 2 4 5 4 5 4 4 5 5 5 5 5 5 4 5 4 5 4 4 4
    47 4 4 4 4 4 3 4 4 5 4 4 5 3 4 4 3 5 2 3 5 4 3
    48 4 4 2 4 3 4 4 4 5 5 4 5 5 5 4 3 5 3 4 2 4 3
    49 4 5 2 5 4 3 4 3 4 4 4 3 5 5 4 3 5 5 5 4 5 3
    50 2 4 2 4 5 5 5 3 4 4 3 4 5 5 4 3 5 3 5 3 4 3
    Basundara Diagnostic Center 51 4 4 3 3 4 3 2 2 3 4 3 2 5 4 3 4 3 2 3 3 4 4
    52 4 3 4 3 4 4 3 2 3 4 3 3 4 4 4 3 3 4 3 3 4 3
    53 4 3 4 3 4 4 3 3 4 3 4 3 5 4 3 2 3 3 3 4 3 5
    54 4 4 3 3 4 4 3 2 4 4 4 3 5 5 4 2 2 4 3 4 4 3
    55 4 3 3 4 3 4 3 3 3 4 2 3 4 4 5 3 3 3 4 5 3 4
    56 4 3 3 4 4 3 4 2 3 4 5 4 4 5 4 3 3 3 4 3 4 3
    57 2 3 3 4 3 4 3 3 3 4 2 4 4 2 3 3 3 4 4 3 4 4
    58 2 2 3 3 4 3 4 3 4 3 4 2 5 4 3 3 4 3 4 4 3 4
    59 2 2 4 4 3 3 3 3 2 2 4 3 4 4 3 4 2 3 4 4 2 4
    60 4 3 3 4 4 5 4 3 3 4 4 5 4 4 3 4 5 4 3 3 4 5
    Khulna Nursing Home 61 3 2 2 1 2 2 3 2 2 4 5 2 2 3 2 2 3 4 4 3 3 5
    62 3 2 1 1 3 5 3 2 2 2 4 5 4 2 3 5 4 4 3 2 2 3
    63 3 3 2 2 3 4 5 3 3 2 1 2 3 1 1 1 4 3 1 2 2 3
    64 3 4 3 3 2 2 4 3 2 1 1 3 2 3 5 4 3 1 2 2 3 4
    65 5 3 2 2 3 1 2 2 3 4 3 2 1 3 2 2 3 1 4 1 2 3
    66 4 3 2 1 3 2 2 3 5 4 3 2 1 1 2 3 4 5 4 3 2 1
    67 3 2 1 1 3 4 1 2 3 4 2 2 2 1 2 3 4 5 3 2 2 3
    68 3 2 2 1 3 2 2 5 4 3 2 1 1 3 2 2 1 3 4 3 2 2
    69 3 2 2 3 4 3 2 2 1 3 2 2 1 5 3 2 2 3 4 4 2 5
    70 4 3 3 4 3 3 4 3 3 3 4 3 5 4 2 3 3 4 3 5 4 4
    Surgical Clinic 71 3 2 2 2 2 3 3 2 1 2 2 1 2 2 3 3 4 2 2 3 2 5
    72 3 2 2 3 1 2 2 3 4 3 3 3 4 2 2 4 2 3 2 1 1 5
    73 2 3 4 3 3 2 2 4 3 3 1 1 2 3 3 4 4 1 1 2 3 4
    74 3 2 1 1 3 4 3 2 2 1 3 4 1 2 2 3 1 4 2 2 3 5
    75 3 3 1 1 3 2 4 5 1 1 2 3 4 1 2 2 4 1 2 2 3 5
    76 4 4 3 3 2 2 2 3 2 3 2 4 4 3 4 2 3 4 5 4 2 3
    77 3 4 3 3 4 2 1 3 1 2 1 3 4 5 4 3 2 1 2 1 3 4
    78 4 4 3 2 3 3 4 1 3 3 3 4 4 5 4 3 2 2 1 3 3 4
    79 4 4 3 2 3 4 4 1 2 2 3 4 4 4 3 2 3 4 4 3 3 4
    80 4 4 3 2 3 3 2 2 3 3 4 3 4 4 4 3 3 4 3 2 4 5
    Cure Home Diagnostic 81 3 2 4 4 5 4 2 4 3 3 4 2 5 5 3 4 2 2 4 3 5 4
    82 3 5 4 3 4 3 2 5 4 2 4 3 3 5 4 3 3 2 4 5 3 5
    83 4 3 2 4 2 5 4 4 3 2 3 4 4 5 3 2 5 2 4 2 4 5
    84 3 4 2 2 4 3 5 4 2 4 3 3 2 4 2 3 5 4 4 2 3 5
    85 2 2 4 3 5 4 3 3 2 4 5 4 3 4 3 5 2 4 4 2 3 5
    86 4 5 4 4 4 3 4 3 5 4 4 3 5 4 3 3 3 4 5 4 5 4
    87 4 4 3 3 4 4 4 3 4 3 3 4 5 4 3 4 3 3 3 4 5 4
    88 4 4 3 3 4 4 3 3 3 4 4 3 5 4 4 3 3 4 5 4 3 5
    89 4 4 3 4 4 3 4 3 3 4 3 3 5 5 4 3 3 4 5 4 3 3
    90 4 3 3 4 5 4 4 3 3 3 4 5 5 4 5 4 3 4 5 4 3 4
    Razin Diagnostic 91 4 4 3 4 4 3 4 2 4 3 4 4 5 4 4 2 3 4 3 3 4 4
    92 4 4 3 3 4 4 4 3 5 4 4 3 5 4 4 3 4 4 3 4 3 5
    93 4 4 3 4 4 4 4 3 4 5 4 3 5 4 3 3 4 4 3 2 4 3
    94 4 4 3 4 4 5 3 4 4 3 4 3 5 5 3 3 3 4 5 4 3 3
    95 4 4 3 4 3 5 4 3 3 3 4 4 3 5 4 3 3 3 3 4 5 4
    96 4 4 3 4 4 4 3 3 4 4 5 4 5 4 4 3 3 3 4 4 4 5
    97 4 4 3 4 4 4 3 3 4 4 5 4 5 5 3 3 3 4 4 3 4 5
    98 4 4 3 4 4 3 3 3 4 5 4 4 5 4 4 3 3 3 4 5 4 3
    99 4 4 3 4 3 5 4 3 4 4 4 3 5 4 4 3 3 4 5 3 3 4
    100 4 4 3 3 4 5 4 3 2 2 3 4 5 5 4 3 3 3 4 5 4 5

    From this database how could you identify how many 4 in a single column with d count? please let me know

    • admin says:

      Use COUNTIF function.

      This formula applies to column A. Range A1 to A9. Change the range as required.

      =COUNTIF(A1:A9;"=4")

Trackbacks

  1. [...] Count cells with multiple criteria in Excel 2007 | Excel Digest [...]