Introducing Excel’s COUNTIF Function

Much of spreadsheet data is a collection of individual transactions or instances that don’t begin to offer meaning until they are organized and summarized in useful ways. Enter Excel’s handy COUNTIF function. When categories of data are mixed together, or if you wish to understand your data based on certain criteria, COUNTIF can help you sort the needed data from the rest or tally only information that falls into specified ranges. When combined with other functions, COUNTIF can become even more useful.

In this example we will show how to count data that falls within a certain range that we specify. Our table shows the monthly levels of contaminates present in an imaginary water sample taken over three years. We want to know how many months in that timeframe the levels were higher than 3.0 PPM (our “warning” level) and how many months the levels fell below 1.0 PPM.

To follow using our example, download Countif Function Excel. These examples apply to Excel 2007 and higher. Images were taken using Excel 2013 on Windows 7.

Countif Funtion Excel

Count Cells That Match a Specified Criteria

The syntax for the COUNTIF function is COUNTIF(range, criteria) where

  • The range argument defines the group of cells that you want counted.
  • The criteria argument defines the circumstances under which a cell will be added to your count. The criteria can be a number, a cell reference, an expression or a text string.

In our example, we will place this example in E4:

=COUNTIF(B3:B43,”>3.0″)
Countif Function Excel - Count Cells That Match a Specified Criteria

  • B3:B43 tells the function to count cells in our “Amount” column
  • “>3.0” tells the function to only count cells if they are greater than 3.0

In the same way, the formula that will go in E5 will be:

=COUNTIF(B3:B43,”<1.0″)
Countif Function Excel - Count Cells That Match a Specified Criteria 2

Use COUNTIF in Formulas

Now that we have COUNTIF working for us, we can combine it with other functions for even more data crunching. Using this same example, we can find out how many months fell between our high and low criteria by subtracting those results from the total. To do this we will combine the simple COUNT function with the functions we just specified to subtract them from the total number of months in our table:

=COUNT(B3:B43)-(COUNTIF(B3:B43,”>3.0″)+COUNTIF(B3:B43,”<1.0″))

  • COUNT(B3:B43) gives us the total number of rows/months
  • (COUNTIF(B3:B43,”>3.0″)+COUNTIF(B3:B43,”<1.0″)) adds together the number of rows/months over 3.0 and under 1.0 which are subtracted from the total number of rows.

Countif Function Excel - Use COUNTIF in Formulas

In this particular example we could have used the COUNTIFS function to specify a count of the rows between the two specified numbers. That formula would look like:

=COUNTIFS(B3:B43,”<3.0″,B3:B43,”>1.0″)

However, it does illustrate how you can use COUNTIF in formulas for data that isn’t numerical, for example, or if you are using a version older than Excel 2007.

Bonus Hint!

If you need to count data in a table that frequently changes – such as new rows added every month – then use a Named Range or a dynamic named range in the range argument. When your table changes, you only have to update the range in one place instead of each of the many places the range is used.

Here is a quick list of tasks that you can use COUNTIF to help you perform:

  • Count cells that have specific numerical relationships such as between two numbers (as above), equal numbers, positive or negative numbers, numbers that are greater and less than a specified value or are specifically not
  • Count cells that begin or end with a specified criteria
  • Count cells that contain specific text or a certain number of characters
  • Count matches in two ranges
  • Find duplicates

What are some ways you have used COUNTIF to get the most out of your data?

Local Seminars Related to this Topic:

Microsoft® Excel®: Beyond the Basics

LIVE ONLINE SEMINARS

Dec. 8

Central Time Zone

Dec. 9

Eastern Time Zone

Dec. 10

Pacific Time Zone

Dec. 14

Central Time Zone

Dec. 15

Pacific Time Zone

Dec. 16

Eastern Time Zone

Dec. 17

Central Time Zone

Jan. 6

Central Time Zone

Jan. 11

Central Time Zone

Jan. 12

Pacific Time Zone

Jan. 14

Eastern Time Zone

Jan. 19

Eastern Time Zone

Jan. 20

Pacific Time Zone

Jan. 21

Central Time Zone

Jan. 25

Central Time Zone

Jan. 27

Central Time Zone

Jan. 28

Eastern Time Zone

Feb. 1

Central Time Zone

Feb. 3

Central Time Zone

Feb. 4

Eastern Time Zone

Feb. 8

Central Time Zone

Feb. 9

Eastern Time Zone

Feb. 11

Central Time Zone

Feb. 15

Central Time Zone

Feb. 17

Eastern Time Zone

Feb. 17

Central Time Zone

Feb. 23

Central Time Zone

Feb. 25

Pacific Time Zone

Mar. 2

Central Time Zone

Mar. 3

Eastern Time Zone

Mar. 8

Central Time Zone

Mar. 10

Central Time Zone

Mar. 15

Central Time Zone

Mar. 17

Central Time Zone

Mar. 18

Eastern Time Zone

Mar. 23

Central Time Zone

Mar. 25

Central Time Zone

Mar. 29

Eastern Time Zone

Mar. 30

Central Time Zone

Apr. 1

Pacific Time Zone

Microsoft® Excel® Basics

LIVE ONLINE SEMINARS

Dec. 8

Eastern Time Zone

Dec. 9

Pacific Time Zone

Dec. 13

Central Time Zone

Dec. 14

Pacific Time Zone

Dec. 15

Eastern Time Zone

Dec. 16

Central Time Zone

Jan. 5

Central Time Zone

Jan. 10

Central Time Zone

Jan. 11

Pacific Time Zone

Jan. 13

Eastern Time Zone

Jan. 18

Eastern Time Zone

Jan. 19

Pacific Time Zone

Jan. 20

Central Time Zone

Jan. 24

Central Time Zone

Jan. 26

Central Time Zone

Jan. 27

Eastern Time Zone

Jan. 31

Central Time Zone

Feb. 2

Central Time Zone

Feb. 3

Eastern Time Zone

Feb. 7

Central Time Zone

Feb. 8

Eastern Time Zone

Feb. 10

Central Time Zone

Feb. 14

Central Time Zone

Feb. 16

Eastern Time Zone

Feb. 16

Central Time Zone

Feb. 22

Central Time Zone

Feb. 24

Pacific Time Zone

Mar. 1

Central Time Zone

Mar. 2

Eastern Time Zone

Mar. 7

Central Time Zone

Mar. 9

Central Time Zone

Mar. 14

Central Time Zone

Mar. 16

Central Time Zone

Mar. 17

Eastern Time Zone

Mar. 22

Central Time Zone

Mar. 24

Central Time Zone

Mar. 28

Eastern Time Zone

Mar. 29

Central Time Zone

Mar. 31

Pacific Time Zone

Advanced Microsoft® Excel®-Macros, PivotTables, Charts and More

LIVE ONLINE SEMINARS

Dec. 8

Central Time Zone

Dec. 9

Central Time Zone

Dec. 14

Eastern Time Zone

Dec. 15

Central Time Zone

Jan. 7

Central Time Zone

Jan. 12

Eastern Time Zone

Jan. 13

Central Time Zone

Jan. 18

Central Time Zone

Jan. 24

Central Time Zone

Jan. 26

Pacific Time Zone

Feb. 2

Eastern Time Zone

Feb. 4

Central Time Zone

Feb. 8

Pacific Time Zone

Feb. 9

Central Time Zone

Feb. 15

Eastern Time Zone

Feb. 18

Central Time Zone

Feb. 24

Central Time Zone

Mar. 3

Central Time Zone

Mar. 8

Eastern Time Zone

Mar. 11

Central Time Zone

Mar. 16

Pacific Time Zone

Mar. 21

Central Time Zone

Mar. 23

Eastern Time Zone

Mar. 31

Central Time Zone

Related Excel® Articles

Categories

Monthly Archives

Leave a Reply

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