[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
The SUMIF function in Google Sheets allows you to sum values in a range based on specific criteria. Whether you’re dealing with financial data, inventory lists, or any other dataset, it can perform calculations efficiently.<\/span><\/p>\n This guide will walk you through the syntax of the SUMIF function, provide practical use cases, and introduce the SUMIFS function to handle multiple criteria.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”syntax” _builder_version=”4.24.1″ _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.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SUMIF(range, criterion, [sum_range])<\/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 The example below sums the values in B1:B10 where the corresponding cells in A1:A10 are greater than 5.<\/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 =SUMIF(A1:A10, “>5”, B1:B10)<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”use” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text module_id=”greater” _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 The <\/span>SUMIF<\/b> function can sum values that are greater than zero.<\/span><\/p>\n This is particularly useful for financial data, where you may want to exclude negative balances or zero values from your totals.<\/span><\/p>\n The example below the function sums all values in A1:A10 that are greater than 0.<\/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 =SUMIF(A1:A10, “>0”)<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”partial” _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 The <\/span>SUMIF<\/b> function can also be used to sum values based on text criteria. You can even include wildcards to consider partial matches.<\/span><\/p>\n This is handy when working with datasets that include text entries, such as product names or categories, and you want to sum related numeric values.<\/span><\/p>\n The example below sums the values\u00a0 in A1:A10 where the cells contain the substring “text”.<\/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 =SUMIF(A1:A10, “*text*”)<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”not” _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 Sometimes, you may need to exclude specific values from your sum.<\/span><\/p>\n The <\/span>SUMIF<\/b> function can sum values that do not equal a criterion, which is great to filter out unwanted data.<\/span><\/p>\n The example below sums the values in A1:A10 where the cells do not equal 5.<\/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 =SUMIF(A1:A10, “<>5”)<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”multiple” _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”]Unfortunately, there is no straightforward way to include several conditions using the <\/span>SUMIF<\/b> function alone.<\/span> =<\/span>SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])<\/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 The example below sums the values in B1:B10 where the corresponding cells in\u00a0 A1:A10 are greater than 0 and the cells in C1:C10 are less than 5.<\/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>\nSyntax<\/h2>\n
\n
Use cases of SUMIF in Google Sheets<\/span><\/h2>\n
SUMIF greater than 0<\/span><\/h3>\n
SUMIF contains partial t<\/span>ext<\/span><\/h3>\n
SUMIF does not equal<\/span><\/h3>\n
Google Sheets SUMIF multiple criteria<\/span><\/h3>\n
\nFor scenarios requiring multiple criteria, Google Sheets provides the <\/span>SUMIFS<\/b> function, which is specifically designed to handle multiple conditions efficiently.<\/span>[\/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