[et_pb_section fb_built=”1″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
COUNTIFS is one of the many Google Sheets count functions. This formula works as its name suggests: it counts values within a range that match all established conditions.<\/span><\/p>\n As we will see, this function is quite simple yet useful for a variety of applications.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”syntax” _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The structure of this function consists of one or more range\/condition pairs. If 3 such pairs are established, the function will check the ranges and count it if all conditions are true.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]\u2026)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n To <\/span>evaluate numbers <\/b>criterias can include any of the comparison operators in the table below.<\/span><\/p>\n So, for example, you could check that values are equal to \u201c1\u201d in column A and more than \u201c20\u201d in column B.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =COUNTIFS(<\/span>A:A<\/span>, <\/span>“=1”<\/span>, <\/span>B:B<\/span>, <\/span>“>20”<\/span>)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n To <\/span>evaluate text <\/b>you don\u2019t need to include any operators, simply include the text in between quotations. The function will look for exact matches.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =COUNTIFS(<\/span>C:C<\/span>, <\/span>“John”<\/span>)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You can even find partial matches with the * wildcard which will match anything. The example below will match any text starting with the word \u201cNew\u201d.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =COUNTIFS(<\/span>C:C<\/span>, <\/span>“John”<\/span>, <\/span>D:D<\/span>, <\/span>“New*”<\/span>)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n To match empty cells all you have to do is include two quotations with nothing in between.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =COUNTIFS(<\/span>C:C<\/span>, <\/span>“John”<\/span>, <\/span>D:D<\/span>, <\/span>“New*”, E:E, “”<\/span>)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n To count cells that aren\u2019t blank, just include the not equal to operator between quotations.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =COUNTIFS(<\/span>C:C<\/span>, <\/span>“John”<\/span>, <\/span>D:D<\/span>, <\/span>“New*”, E:E, “<>”<\/span>)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You can even input Boolean values (TRUE and FALSE) as conditions. Keep in mind, avoid using quotations so Google Sheets recognizes these as Booleans and not text.<\/span><\/p>\n This is particularly useful if you have checkboxes as they are just a visual representation of these values. If a checkbox is empty it\u2019s FALSE and if\u2019t filled it is TRUE.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =COUNTIFS(<\/span>A:A<\/span>, <\/span>TRUE<\/span>, <\/span>B:B<\/span>, <\/span>FALSE<\/span>)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Usually you include columns as ranges, meaning Google Sheets will evaluate one row at a time. But you could turn this around and include rows in the ranges, in this case the COUNTIFS will evaluate the conditions one column at a time.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =COUNTIFS<\/span>(<\/span>1:1<\/span>, <\/span>“>20”<\/span>, <\/span>2:2<\/span>, <\/span>TRUE<\/span>)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Using dates to exclude values from the count is quite simple, just express them as you would any other value. <\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =COUNTIFS(<\/span>C4:C12<\/span>, <\/span>“>01\/01\/2016”<\/span>, <\/span>C4:C12<\/span>, <\/span>“<01\/01\/2017”<\/span>)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n If you are looking for new and better ways to manage your data, Sheetgo is a great solution. Connect your data sources and process your information.<\/span><\/p>\nSyntax<\/span><\/h2>\n
\n
Evaluate numbers<\/span><\/h3>\n
\n\n
\n ><\/span><\/td>\n Greater than<\/span><\/td>\n<\/tr>\n \n >=<\/span><\/td>\n Greater than or equal to<\/span><\/td>\n<\/tr>\n \n =<\/span><\/td>\n Equal to<\/span><\/td>\n<\/tr>\n \n <><\/span><\/td>\n Not equal to<\/span><\/td>\n<\/tr>\n \n <<\/span><\/td>\n Less than<\/span><\/td>\n<\/tr>\n \n <=<\/span><\/td>\n Less than or equal to<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Evaluate text<\/span><\/h3>\n
Match blank cells<\/span><\/h3>\n
COUNTIFS not blank<\/span><\/h3>\n
Add boolean expressions<\/span><\/h3>\n
COUNTIFS across columns<\/span><\/h3>\n
COUNTIFS between two dates<\/span><\/h3>\n
Sheetgo connections<\/span><\/h2>\n