Vertex42 The Excel Nexus

Table 3-3 contains formulas that demonstrate a variety of counting techniques. Table 3-3: COUNTING FORMULA EXAMPLES

Open table as spreadsheet

Table 3-3 contains formulas that demonstrate a variety of counting techniques. Table 3-3: COUNTING FORMULA EXAMPLES

Open table as spreadsheet

Formula |
Description |

=COUNTIF(Region,"North") |
Counts the number of rows in which Region = "North" |

=COUNTIF(Sales,300) |
Counts the number of rows in which Sales = 300 |

=COUNTIF(Sales,">300") |
Counts the number of rows in which Sales > 300 |

Formula |
Description |

=COUNTIF(Sales,"<>100") |
Counts the number of rows in which Sales <> 100 |

=COUNTIF(Region,"?????") |
Counts the number of rows in which Region contains five letters |

=COUNTIF(Region,"*h*") |
Counts the number of rows in which Region contains the letter H (not case-sensitive) |

=COUNTIFS(Month,"Jan",Sales,">200") |
Counts the number of rows in which Month = "Jan" and Sales > 200 (Excel 2007 only) |

{=SUM((Month="Jan")*(Sales>200))} |
An array formula that counts the number of rows in which Month = "Jan" and Sales > 200 |

=COUNTIFS(Month,"Jan",Region,"North") |
Counts the number of rows in which Month = "Jan" and Region = "North" (Excel 2007 only) |

{=SUM((Month="Jan")*(Region="North"))} |
An array formula that counts the number of rows in which Month = "Jan" and Region = "North" |

=COUNTIFS(Month,"Jan",Region,"North")+ COUNTIFS(Month,"Jan",Region,"South") |
Counts the number of rows in which Month = "Jan" and Region = "North" or "South" (Excel 2007 only) |

{=SUM((Month="Jan")*((Region="North")+ (Region="South")))} |
An array formula that counts the number of rows in which Month = "Jan" and Region = "North" or "South" |

=COUNTIFS(Sales,">=300",Sales,"<=400") |
Counts the number of rows in which Sales is between 300 and 400 (Excel 2007 only) |

{=SUM((Sales>=300)*(Sales<=400))} |
An array formula that counts the number of rows in which Sales is between 300 and 400 |

Was this article helpful?

## Post a comment