Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • Excel Bar Chart
  • Example of COUNTIFS with variable table column in Excel
  • Understanding Pivot Tables in Excel
  • Conditional Formatting Rules in Excel

References

  • Count unique text values with criteria
  • Left Lookup in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • How to get first row number in range in Excel

Data Validations

  • Data validation must not exist in list
  • Excel Data validation number multiple 100
  • Excel Data validation only dates between
  • Excel Data validation require unique number
  • Excel Data validation date in next 30 days

Count numbers by range with COUNTIFS in Excel

by

This tutorial shows how to Count numbers by range with COUNTIFS in Excel using the example below;

Formula

=COUNTIFS(range,">=low",range,"<=high")

Explanation

To count numeric data by range or grouping, you can buildĀ a summary table and use COUNTIFS to count values at each threshold.

In the example show, we have a list of names and ages and are using the COUNTIFs function to generate a count of ages into 6 brackets. The formula in cell F5 is:

=COUNTIFS(ages,">=20",ages,"<=29")

How this formula works

The named range “ages” refers to C5:C304.

Worked Example:   Count items in list in Excel

The COUNTIFS function lets you count values that meet multiple criteria with an AND relationship (i.e. all criterial must be true).

Worked Example:   Count cells between two numbers in Excel

In this case, we want to group the data by age range into 10-year brackets, so we use COUNTIFS as follows in column F:

=COUNTIFS(ages,">=20",ages,"<=29") // 20-29
=COUNTIFS(ages,">=30",ages,"<=39") // 30-39
=COUNTIFS(ages,">=40",ages,"<=49") // 40-49

For the final bracket, 70+, we only use one criteria:

=COUNTIFS(ages,">=70") // 70+

Dynamic ranges

To expose ranges values on the worksheet where they can be easily changes, you can join references to logical operators with concatenation like this:

=COUNTIFS(ages,">="$A1,ages,"<="&B1)

This formula counts greater than or equal to (>=) the value in A1 and less than or equal to (>=)the value in B1.

Worked Example:   Count dates in current month in Excel

With a Pivot Table

Pivot Tables offer automatic grouping into equal sizeĀ ranges.

Post navigation

Previous Post:

44 Practical Excel IF function Examples

Next Post:

Large with criteria in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Complete List of Excel Logical Functions, References and Examples
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • Return blank if in Excel
  • IF with boolean logic in Excel
  • Excel If, Nested If, And/Or Criteria Examples

Date Time

  • How to calculate workdays per month in Excel
  • Get date from day number in Excel
  • How to calculate nth day of week in month in Excel
  • Add workdays to date custom weekends in Excel
  • Custom weekday abbreviation in Excel

Grouping

  • Map inputs to arbitrary values in Excel
  • Categorize text with keywords in Excel
  • Group times into unequal buckets in Excel
  • Map text to numbers in Excel
  • How to randomly assign data to groups in Excel

General

  • How to create dynamic worksheet reference in Excel
  • How to calculate percent variance in Excel
  • How to calculate percentage of total in Excel
  • How to generate random number between two numbers in Excel
  • 3D SUMIF for multiple worksheets in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning