[et_pb_section fb_built=”1″ module_class=”sheetgo-post” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_row _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
A common task in Excel often involves taking a set of data and tallying the number of occurrences that match certain characteristics. The COUNTIF Excel function<\/a> allows you to do just that: it counts the number of data points (cells) that meet a certain criteria that you specify within the function.<\/p>\n Because of the wide range of criteria that you can input, the COUNTIF function allows you to filter your data in virtually any way you want.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=COUNTIF(range, criteria)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n To illustrate some of the ways that you can use the COUNTIF function in Excel, take a look at the dataset below. It outlines the dietary preferences of a group of people. In the first example, I use COUNTIF to tally the number of vegetarians in the group by typing =COUNTIF(D2:D9,”Yes”)<\/b>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/1.-Countif-text-string.png” alt=”countif excel 1″ title_text=”1. Countif text string” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In this example, the criterion that I\u2019ve specified is a text string. The function looks at all the cells in the range that I\u2019ve indicated, and returns the number of cells that match the criteria \u2013 in this case, the number of instances where “Yes” appears in the column.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Another way of indicating the criteria is to use a cell reference. Take a look at the example below. Here, I count the number of people with allergies by using cell E4 as my criteria. Of course, that cell contains the text string Yes, so this is just an alternative to typing \u201cYes\u201d into the formula.<\/p>\n The formula I use here is =COUNTIF(E2:E9,E4)<\/b>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/2.-Countif-cell-ref.png” alt=”countif excel 2″ title_text=”2. Countif cell ref” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You can also put a number as your criteria in the COUNTIF function. In this example, I want to count only people of a certain age, so I put the age directly into the function.<\/p>\n Notice that unlike a text string,\u00a0 you don\u2019t need to put quotation marks around a number in a formula.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/3.-Countif-number.png” alt=”countif excel 3″ title_text=”3. Countif number” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Another thing I’ve done differently here is: I added two COUNTIF functions together. The reason for this is is that I want to count all the people aged 20 and 21.<\/p>\n In order to do this, I use two separate COUNTIF functions. This may sound cumbersome, but the good news is that because COUNTIF just gives a final tally of cells that meet the criteria, you can use multiple COUNTIF functions in the same formula. You simply add them together to get the total count of all cells that match multiple criteria. You can also subtract COUNTIF functions when you want to get cells that are between two criteria.<\/p>\nSyntax<\/h3>\n
\n
How to use COUNTIF in Excel<\/h3>\n
Criteria as text string<\/h4>\n
Criteria as cell reference<\/h4>\n
Criteria as a number<\/h4>\n