[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
Learning to use Sumifs with multiple criteria is key for any Google Sheet user looking to analyze data. It enables you to sum data only if several conditions are met, thus allowing you to segregate data by categories.<\/span><\/p>\n In this article we will see how this function works and look at advanced uses. We will also delve into how it can be complemented with Sheetgo features to bring your spreadsheet game to the next level.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”understanding” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This function is designed to <\/span>add values in a range based on one or multiple criteria<\/b>. For values to be included in the sum, all conditions must be met.<\/span><\/p>\n Unlike SUMIF, <\/span>SUMIFS can accommodate numerous conditions from different columns<\/b>, making it better for complex data analysis tasks.<\/span><\/p>\n This is the basic syntax of the SUMIFS Google Sheets function:<\/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 =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 Consider a simple scenario where you want to add values within a column that are between 10 and 20. All the values are in column A, and both criteria also point to this column. This is what the formula would look like:<\/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 =SUMIFS(<\/span>A:A<\/span>, <\/span>A:A<\/span>, <\/span>“>10”<\/span>, <\/span>A:A<\/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 Learning how to use SUMIFS is indispensable if you are looking to use spreadsheets for complex analysis.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”advanced” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now we will look at a few advanced uses of SUMIFS, across the following examples we will be centered around the data shown below<\/b>.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/03\/sumifs-with-multiple-criteria_1.webp” alt=”sumifs with multiple criteria 1″ title_text=”sumifs-with-multiple-criteria_1″ align=”center” _builder_version=”4.24.1″ _module_preset=”default” width=”100%” custom_margin=”30px||30px||true|false” border_radii=”on|12px|12px|12px|12px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”incorporating” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You want to sum up sales for the “Toys & Games” category,\u00a0 made by “Dave Brown” and paid with a “Debit Card.”<\/span><\/p>\n This is what the formula to achieve this would look like:<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” custom_padding=”||0px|||” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SUMIFS(<\/span>C:C<\/span>, <\/span>B:B<\/span>, <\/span>“Toys & Games”<\/span>, <\/span>E:E<\/span>, <\/span>“Dave Brown”<\/span>, <\/span>F:F<\/span>, <\/span>“Debit Card”<\/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 Just like this you could keep adding criteria to refine the sum that is being carried out. Building on this past example, let\u2019s say now you only want to add sales of items that cost over $10.<\/span><\/p>\n You would have to adapt the formula like this:<\/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 =SUMIFS(<\/span>C:C<\/span>, <\/span>B:B<\/span>,<\/span>“Toys & Games”<\/span>, <\/span>E:E<\/span>, <\/span>“Dave Brown”<\/span>, <\/span>F:F<\/span>, <\/span>“Debit Card”<\/span>, <\/span>C:C<\/span>, <\/span>“>10”<\/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 could also exclude a particular item from the sum by adding a criterion with the not equal to operator (\u201c<>\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 =SUMIFS(<\/span>C:C<\/span>, <\/span>B:B<\/span>, <\/span>“Toys & Games”<\/span>, <\/span>E:E<\/span>, <\/span>A:A<\/span>, <\/span>“<>Blox City Space Rocket”<\/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 A slight modification of the last example can help you rule out items with missing data values. For this use case, they could have blank product names.<\/span><\/p>\n This is how you exclude these items from the sum:<\/span><\/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 =SUMIFS(<\/span>C:C<\/span>, <\/span>B:B<\/span>, <\/span>“Toys & Games”<\/span>, <\/span>E:E<\/span>, <\/span>A:A<\/span>, <\/span>“<>”<\/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 If you want to sum values based on a condition involving a date things get a little more complicated. This is how you would add only sales made starting in 2023.<\/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 =SUMIFS(<\/span>C:C<\/span>, <\/span>B:B<\/span>, <\/span>“Toys & Games”<\/span>, <\/span>A:A<\/span>, <\/span>“<>”<\/span>, <\/span>D:D<\/span>, <\/span>“>”<\/span>&DATE(<\/span>2023<\/span>,<\/span>1<\/span>,<\/span>1<\/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 Here you have to use two new resources:<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”utilizing” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now you want to analyze sales for a product and you only remember that its name has the word “Blox”. This is a problem, you never remember the full name. But don\u2019t worry, all you have to do is include a criterion with partial text.<\/span><\/p>\n To accomplish this, you have to use the wildcard character (*) in your criterion:<\/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 =SUMIFS(<\/span>C:C<\/span>, <\/span>A:A<\/span>, <\/span>“*Blox*”<\/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 This formula doesn\u2019t make a good use of the function because it only includes one criteria.<\/span><\/p>\n Let\u2019s include parts of the previous formula to make the most of SUMIFS:<\/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 =SUMIFS<\/span>(<\/span>C:C<\/span>, <\/span>A:A<\/span>, <\/span>“*Blox*”<\/span>,\u00a0 <\/span>E:E<\/span>, <\/span>“Dave Brown”<\/span>, <\/span>F:F<\/span>, <\/span>“Debit Card”<\/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 These examples illustrate the versatility of the SUMIFS function in Google Sheets, enabling users to perform comprehensive data analysis with multiple conditions and partial matches.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”elevating” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Sheetgo offers features that complement the SUMIFS function in Google Sheets. Adding a robust solution for data analysis, management, and collaboration.<\/span><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][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 module_id=”connections” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Sheetgo elevates the SUMIFS function by automating the collection and aggregation of data from multiple Google Sheets. Set up connections from different sources and gather information in one spreadsheet before using SUMIFS to analyze it.<\/span><\/p>\n Sheetgo connections allow users to seamlessly integrate data from various sources without the manual effort of compiling data sets.<\/span><\/p>\n For instance, if sales data is spread across multiple sheets for different regions, Sheetgo can automatically consolidate this data into a single sheet, ready for analysis with SUMIFS.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”forms” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Sheetgo enhances the SUMIFS function’s utility in Google Sheets by integrating forms for easy collaboration. This feature simplifies data entry and collection, allowing users to submit information through custom forms that automatically populate a centralized spreadsheet.<\/span><\/p>\n For example, a sales team can use Sheetgo forms to input daily sales data, customer feedback, or inventory updates. These submissions are then automatically aggregated into a master sheet, ready for analysis.<\/span><\/p>\n By leveraging forms for data collection, Sheetgo fosters a collaborative environment where information is seamlessly shared and updated.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”workflows” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Sheetgo’s automated workflows are a cornerstone for enhancing operational efficiency within organizations. These workflows streamline business processes by automating the transfer and synchronization of data across various Google Sheets.\u00a0<\/span><\/p>\n For example, in a project management scenario, updates to project timelines or task completions in individual project sheets can automatically update a master project dashboard, providing managers with an up-to-date overview of all projects in real-time.<\/span><\/p>\n Furthermore, Sheetgo\u2019s workflows can be customized to trigger specific actions based on data changes, such as sending alerts or emails when certain thresholds are met or tasks are due. This level of automation extends beyond simple data consistency, actively facilitating task management and accountability within teams.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”conclusion” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now you know the basics of how the SUMIFS function operates and some more advanced applications. Additionally, you discovered how integrating Sheetgo’s features can significantly enhance spreadsheet capabilities.<\/span><\/p>\n By combining these techniques, users can unlock the full potential of Google Sheets, leading to more informed decision-making and streamlined workflows.<\/span><\/p>\nUnderstanding SUMIFS function in Google Sheets<\/span><\/h2>\n
\n
Advanced uses of SUMIFS<\/span><\/h2>\n
Incorporating multiple criteria across different columns<\/span><\/h3>\n
Sumifs not equal to text<\/span><\/h4>\n
Sumifs not blank<\/span><\/h4>\n
Sumifs greater than date<\/span><\/h4>\n
\n
Utilizing wildcard characters for partial matches<\/span><\/h3>\n
Elevating SUMIFS with Sheetgo<\/span><\/h2>\n
Connections to simplify data collection<\/span><\/h3>\n
Forms for easy collaboration<\/span><\/h3>\n
Workflows to automate business processes<\/span><\/h3>\n
Conclusion and further resources<\/span><\/h2>\n