98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum 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?
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
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
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
countif
Is it possible to do nested countif functions?
countif
How do you count if for more than one if? Count if row1 = 1 AND row2<30?
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.
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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |