How to count cells by criteria

Use COUNTIF if you need to sum values for a particular person or other criterion.

To count cells by criteria, do the following:

    1.    Select the cell where want Excel to return the number of cell by criteria.

COUNTIF example Excel 2016

    2.    Do one of the following:

  • On the Formula tab, in the Function Library group, select the More Functions button and then select Statistical:
    Formula Excel 2016

    Choose COUNTIF in the list.

  • Click the Insert Function button Insert Function button in the left of the edit bar:
    Edit bar Excel 2016

    In the Insert Function dialog box:

    Insert Function Excel 2016
    • select Statistical in the Or select a category listbox
    • select COUNTIF in the Select a function list

    3.    In the Function Arguments dialog box, full following fields:

Insert Function Excel 2016
  • The Range field determines the range of cells Excel will look to perform the count in. In this example the cell range is D2:D21.
  • The Criteria is a conditional statement that is similar to the conditional statement in the IF statement.

    4.    Press OK.

COUNTIF example Excel 2016

Note: You can enter this formula using keyboard, for this example:

=COUNTIF(D2:D21,">200")

If you want to use COUNTIF on a selection of cells (not necessarily one solid range), summing multiple COUNTIFs:

=SUM(COUNTIF(D3,">100"),COUNTIF(D7,">100"),COUNTIF(D14,">100"),COUNTIF(D17,">100"))

Note:

  • You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
  • Microsoft Excel provides additional functions that can be used to analyse your data based on a condition.
    • To calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function (see How to sum cells by criteria for more details).
    • To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF worksheet function.
    • To count cells that are empty or not empty, use the COUNTA and COUNTBLANK functions.

Thank you for visiting OfficeToolTips

We are glad to help you in your work. However, you are using ad blocker and our efforts will not be rewarded.

Please consider disabling ad blocker before continuing the reading.