Excel countif

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Topics » Excel countif

Excel countif

The Excel Countif function counts only those items in a range that match criteria you set.

For example, suppose a group of children choose between one of 3 destinations Paris, Florida or New York and enter their choice of destination next to their name in an Excel spreadsheets.

Name Destination
Peter Paris
Sue NY
John Paris
Wendy Florida
Richard Paris
Roger NY


The Countif formula can be used to count how many have opted for Paris

=countif(A2:A7,"Paris")
returns the result 3

=countif(A2:A7,"NY")
returns the result 2 for those choosing NY.

Note the word Paris is in quotes because it is text.

There are several other related count functions in Excel:

=Count
Counts the total number of numeric non blank entries

=Counta
Counts the total number of non blank enries (numeric and text entries)

=Countblank
Counts the number of blank entries

=Countifs (version Excel 2007)
Counts those items in a range that match multiple criteria you set.

See below for related articles on the Count function.

Related forum posts:

Using count function with filtered tables

I have a table which I use filters on. Is there a way to apply the count function so that it counts the filtered results?

Read forum post

 

Counting different products in a column

Hi, I have a spreadsheet that has columns of different items and I need to find out how many of each item are in the column. Ex: green blue yellow white green blue I need totals for each colour. Is there a way of doing this? Cheers, Dee

Read forum post

 

Pivot Tables

From pivot table below how do I do a count of the number of 6 figure digits below 9,510 9,530 and 9,540 in the first column. Column Labels 4 Row Labels Sum of ValueCOCur Count of ValueCOCur2 9,510 7,639 25 142,939 1,516 5 178,046 3,923 12 191,249 2,199 8 9,530 1,897 1 (blank) 1,897 1 9,540 19,647 57 140,831 817 3 141,128 790 3

Read forum post

 

Other posts you might be interested in:

Duplications

Hi, Please could you help. I have a sheet of data and want to quickly identify and duplications so that they can be removed. What is the quickest way of doing this? Many thanks. John

Read forum post

 

countif

Is it possible to do nested countif functions?

Read forum post

 

countif

How do you count if for more than one if? Count if row1 = 1 AND row2<30?

Read forum post

 

Related articles

Learn The Secrets Of COUNT Functions In Excel

Once you've created calculations in Excel you might then have moved on to use Excel's own built in calculations in the form of functions. A popular Excel function is COUNT and this article summarises the different COUNT functions available and describes how each are used.

Read article

 

 

Training courses

 

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


Server loaded in 0.08 secs.