The COUNTIF function in Excel is used to determine how many entries in a list meet a certain criteria.\u00a0 The criteria can be numeric, such as \u201cgreater than 100\u201d or text, such as how many start with a certain letter or contain a certain piece of text.<\/p>\n
The basic format is =COUNTIF(range of cells to evaluate<\/em>, criteria<\/em>).\u00a0 The range of cells can be an actual cell reference or a range name.\u00a0 Let\u2019s look at some examples of how we can put it to use.<\/p>\n A<\/p>\n<\/td>\n B<\/p>\n<\/td>\n C<\/p>\n<\/td>\n<\/tr>\n 60.00<\/p>\n<\/td>\n<\/tr>\n 60.00<\/p>\n<\/td>\n<\/tr>\n 110.00<\/p>\n<\/td>\n<\/tr>\n 100.00<\/p>\n<\/td>\n<\/tr>\n 60.00<\/p>\n<\/td>\n<\/tr>\n Here is the simplest example of how to use COUNTIF.\u00a0 Let\u2019s say you want to know how many of the sales were exactly $60.\u00a0 In the spreadsheet above, the formula =COUNTIF(C1:C5,60)<\/strong><\/em> would return the answer 3.\u00a0 C1:C5 represents cells 1 through 5 of column C, and 60 is the number we want Excel to count.<\/p>\n In the previous example, Excel assumes that your criteria is \u201cequal to\u201d.\u00a0 For anything else, you need to include your criteria in the formula.\u00a0 You also need to surround the criteria with quotation marks.\u00a0 So if you want to know how many sales were greater than or equal to $100, the formula would look like this =COUNTIF(C1:C5,”>=100″)<\/strong><\/em> .<\/p>\n The rules for evaluating text are similar, but need a little explanation.\u00a0 First the text you want Excel to look for always needs to be surrounded with quotation marks.\u00a0 So if you want to count the number of times Blue widget<\/em> occurs in the description column, you could use the formula =COUNTIF(B1:B5,”Blue Widget”)<\/strong><\/em>.\u00a0 That will show only descriptions exactly equal to Blue widget<\/em>, which is 1 in this example.<\/p>\n What if the items you want to count don\u2019t all have exactly the same description?\u00a0 In these situations, you can use the * as a wildcard.\u00a0 So if you enter the formula =COUNTIF(B1:B5,”Blue*”)<\/strong><\/em> you will discover that only 1 entry starts with blue.\u00a0 Or you can add another wildcard in front of the word blue, like this, =COUNTIF(B1:B5,”*Blue*”)<\/strong><\/em> and the result will be 2, because two lines have blue somewhere in the desctiption.\u00a0 And\u00a0 =COUNTIF(B1:B5,”*Blue”)<\/strong><\/em> will show that none of the descriptions end with blue.<\/p>\n I want to talk about one last variation.\u00a0 In all of these examples, we have put the number or text that we want Excel to use for comparison right in the formula.\u00a0 But you may want to count the entries that match the contents of another cell.\u00a0 Counting entries that exactly<\/span> match another cell is much like our first simple example.\u00a0 You just substitute a cell address for the number.\u00a0 To find out how many sales occurred on the date that is entered is cell A7 (2 in this example), the formula would be =COUNTIF(A1:A5,A7)<\/strong><\/em>.\u00a0 But things get tricky when your comparison is anything other than \u201cequal to\u201d.\u00a0 When comparing to another cell, the operator needs to be in quotes, such as \u201c>=\u201d and the & character needs to precede the cell address.\u00a0 So the formula to count the number of sales that occurred on or after the date in cell A7 would be =COUNTIF(A1:A5,”>=”&A7)<\/strong><\/em>.<\/p>\n","protected":false},"excerpt":{"rendered":" Have you ever wanted to know how many items in a list met a certain condition? Excel\u2019s COUNTIF function can help you answer questions like \u201cHow many are more than $100\u201d or \u201cHow many have the word blue in the description?\u201d<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[5],"tags":[],"_links":{"self":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/423"}],"collection":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/comments?post=423"}],"version-history":[{"count":5,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/423\/revisions"}],"predecessor-version":[{"id":621,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/423\/revisions\/621"}],"wp:attachment":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/media?parent=423"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/categories?post=423"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/tags?post=423"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\n\n
\n <\/td>\n \n \n \n \n 1<\/td>\n 1\/15\/2011<\/td>\n White widget<\/td>\n \n \n 2<\/td>\n 1\/20\/2011<\/td>\n Blue widget<\/td>\n \n \n 3<\/td>\n 2\/1\/2011<\/td>\n Yellow gadget<\/td>\n \n \n 4<\/td>\n 2\/1\/2011<\/td>\n White and blue widget twin pack<\/td>\n \n \n 5<\/td>\n 2\/18\/2011<\/td>\n Red widget<\/td>\n \n \n 6<\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n \n 7<\/td>\n 2\/1\/2011<\/td>\n <\/td>\n <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n