### Count function examples

1999-08-14 Count 0 119

In Excel you can count the content of cells that meet different criteria in several different ways. These are some of the functions that can be used:

- COUNT

Counts the number of cells in a range that contains numeric values. - COUNTA

Counts the number of nonblank cells in a range. - COUNTBLANK

Counts the number of blank cells in a range. - COUNTIF

Counts the number of cells in a range that meet a given criteria. - An array formula can also be useful in cases that none of the above functions are suitable.
- DCOUNT

This database-function requires values stored in a table with columnheadings and a separate criteria range to describe which values are to be counted.

More information about database functions.

Description | Formula |
---|---|

Counts the values in the range E4:E23 | =COUNT(E4:E23) |

Counts all non-blank cells in the range E4:E23 | =COUNTA(E4:E23) |

Counts blank cells in the range E4:E23 | =COUNTBLANK(E2:E23) |

Counts all values greater than 0 | =COUNTIF(E4:E23;">0") |

Counts all values less than 0 | =COUNTIF(E4:E23;"<0") |

Counts all cells containing the text yes |
=COUNTIF(F4:F23;"yes") |

Counts all cells containing text starting with k |
=COUNTIF(F4:F23;"k*") |

Counts all cells containing text with the letter a |
=COUNTIF(F4:F23;"*a*") |

Counts all cells containing the text yes or no |
=COUNTIF(F4:F23;"yes")+COUNTIF(F4:F23;"no") |

Same as above with an array formula | {=SUM((F4:F23="yes")+(F4:F23="no"))} |

Counts all cells containing 3 characters | =COUNTIF(F4:F23;"???") |

Counts all cells with values between 30 and 80 | =COUNTIF(E4:E23;">=30")-COUNTIF(E4:E23;">80") |

Counts all unique numbers in the range | =SUM(IF(FREQUENCY(E4:E23;E4:E23)>0;1;0)) |

Counts all odd numbers in the range (array formula) | {=SUM(MOD(G4:G23;2))} |

Counts all even numbers in the range (array formula) | {=SUM(IF(MOD(G4:G23;2)=0;1;0))} |

Counts cells with an AND-criteria (array formula) | {=SUM(IF((F4:F23="yes")*(G4:G23>10);1;0))} |

Counts cells with an AND-criteria (array formula) | {=SUM((F5:F24="yes")*(G5:G24>10))} |

Counts cells with two AND-criterias (array formula) | {=SUM((F5:F24="yes")*(G5:G24>10)*(A5:A24="North"))} |

Counts cells with an OR-criteria (array formula) | {=SUM((F4:F23="yes")+(F4:F23="no"))} |