[et_pb_section fb_built=”1″ admin_label=”section” _builder_version=”4.16″ 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 admin_label=”row” _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||” 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.<\/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.<\/p>\n
[\/et_pb_text][et_pb_text module_id=”sumif-and-sumifs-syntax-in-google-sheets” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Let\u2019s review the syntax for SUMIF and SUMIFS before using them.<\/p>\n
Google Sheets\u2019 SUMIF function has two required parameters and an optional parameter.<\/p>\n
\n=SUMIF(range, criterion,[sum_range])<\/p>\n<\/blockquote>\n
\n
- range:<\/strong> the range of cells that you want to test using the criterion.<\/li>\n
- criterion:<\/strong> the test or condition that you want to apply to the range.<\/li>\n
- sum_range:<\/strong> the range you want to sum if different from the range used as the first parameter.<\/li>\n<\/ul>\n
SUMIFS syntax: the sum range comes first<\/h3>\n
Google Sheets\u2019 SUMIFS function has three required parameters. However, SUMIFS allows you to provide additional parameters to test multiple ranges using different criteria.<\/p>\n
\n=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])<\/p>\n<\/blockquote>\n
Watch the order: in SUMIFS the sum range comes first, while in SUMIF it comes last and is optional. The sum_range, the first range, and the first criterion are required arguments.<\/p>\n
\n
- sum_range:<\/strong> the range you want to sum.<\/li>\n
- criteria_range1:<\/strong> the range of cells that you want to test using the first criterion.<\/li>\n
- criterion1:<\/strong> the test or condition that you want to apply to the first range.<\/li>\n<\/ul>\n
Additional ranges and criteria are optional, but you can add as many as you want. However, the dimensions of subsequent ranges must be equal to those of the first range – all ranges should have the same number of rows and columns.<\/p>\n
\n
- criteria_range2*:<\/strong> the second range of cells that you want to test using the second criterion.<\/li>\n
- criterion2*:<\/strong> the test or condition you want to apply to the second range.<\/li>\n<\/ul>\n
See more examples in our guide to SUMIFS with multiple criteria.<\/a><\/p>\n
[\/et_pb_text][et_pb_text module_id=”how-to-write-sumif-and-sumifs-criteria” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
How to write SUMIF and SUMIFS criteria<\/h2>\n
There are many useful operators you can use to define the criteria or conditions in the SUMIF and SUMIFS functions. For example, these two formulas add up the values next to non-blank cells and next to blank cells, respectively.<\/p>\n
\n=SUMIF(range, "<>", sum_range)
=SUMIF(range, "", sum_range)<\/p>\n<\/blockquote>\nNumbers and comparison operators<\/h3>\n
When dealing with exact numeric values, you don\u2019t need to use quotes. However, if you’re using comparison operators, you\u2019ll need the quotes to enclose the condition or criterion.<\/p>\n
\n
- <><\/strong> not equal<\/li>\n
- =<\/strong> equal<\/li>\n
- >=<\/strong> greater than or equal<\/li>\n
- <=<\/strong> less than or equal<\/li>\n
- ><\/strong> greater than<\/li>\n
- <<\/strong> less than<\/li>\n<\/ul>\n
Text and wildcards<\/h3>\n
You also have a lot of options in terms of text values. In addition to matching exact text by using quotation marks, you can also use any of the following wildcards for partial matches.<\/p>\n
\n
- *<\/strong> Matches 0 or more characters.<\/li>\n
- ?<\/strong> Matches a single character.<\/li>\n
- ~<\/strong> If you need to search for the symbols \u201c*\u201d or \u201c?\u201d, add the tilde before the symbol to escape it, so it’s not used as a wildcard.<\/li>\n<\/ul>\n
[\/et_pb_text][et_pb_text module_id=”how-to-write-a-sumif-formula-step-by-ste” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
How to write a SUMIF formula step by step<\/h2>\n
Now that you have reviewed the function\u2019s syntax, it’s time to see some examples of how to use SUMIF in different situations.<\/p>\n
Imagine you have a table with sales data, which includes the date, the name of the sales agent, their team, the product, and the sales amount. You need several totals for your sales report, and adding them up by hand would take ages, as the table has five hundred rows.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumif-google-sheets-sales-data.webp” alt=”SUMIF Google Sheets \u2014 sales table with date, sales_agent, product, sales_team, and sales_amount columns” title_text=”Sales table with date, sales_agent, product, sales_team, and sales_amount columns” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SUMIF with a text criterion<\/h3>\n
For this example, I want to sum the sales amounts corresponding to \u2018productA\u2019.<\/p>\n
1.<\/strong> In an empty cell, type the SUMIF formula.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumif-google-sheets-type-formula.webp” alt=”SUMIF formula in Google Sheets \u2014 =SUMIF( typed in cell G2 under sales total productA” title_text=”=SUMIF( typed in cell G2 under sales total productA” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
2.<\/strong> Select the range to which you want to apply the criterion. In this case, the column with the products.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumif-google-sheets-select-product-range.webp” alt=”SUMIF formula in Google Sheets \u2014 product column C:C selected as the range” title_text=”Product column C:C selected as the range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
3.<\/strong> Add the criterion you want to apply to the selected range. For this example, I want an exact match for \u201cproductA\u201d.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumif-google-sheets-text-criterion.webp” alt=”SUMIF formula in Google Sheets \u2014 productA typed as the criterion” title_text=”ProductA typed as the criterion” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
4.<\/strong> Next, select the range with the values you want to sum if it’s different from the first range selected. In this case, it is different, as I want to add the values in the sales amount column.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumif-google-sheets-select-sum-range.webp” alt=”SUMIF formula in Google Sheets \u2014 sales_amount column E:E selected as the sum range” title_text=”Sales_amount column E:E selected as the sum range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
5.<\/strong> That’s it. =SUMIF(C:C,"productA",E:E) returns $119,308.93, the total for sales of productA.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumif-google-sheets-text-result.webp” alt=”SUMIF Google Sheets result \u2014 $119,308.93 total for productA in cell G2″ title_text=”$119,308.93 total for productA in cell G2″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SUMIF with a date range<\/h3>\n
Imagine that you need to sum values based on the sales date rather than the product or any other text value. For this example, I will find the total amount for sales made on or after February 1st, 2022.<\/p>\n
1.<\/strong> Type the SUMIF function in an empty cell.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumif-google-sheets-date-type-formula.webp” alt=”SUMIF date range in Google Sheets \u2014 =SUMIF( typed in cell G5 under Sales after Feb.1st” title_text=”=SUMIF( typed in cell G5 under Sales after Feb.1st” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
2.<\/strong> Select the range to which you want to apply the criterion. In this example, I\u2019ll select the column with the dates.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumif-google-sheets-date-select-range.webp” alt=”SUMIF date range in Google Sheets \u2014 date column A:A selected as the range” title_text=”Date column A:A selected as the range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
3.<\/strong> The criterion needs to check whether the date is greater than or equal to February 1st, 2022.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumif-google-sheets-date-criterion.webp” alt=”SUMIF date range in Google Sheets \u2014 on or after 2\/1\/22 typed as the criterion” title_text=”On or after 2\/1\/22 typed as the criterion” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
4.<\/strong> Next, you need to select the range with the values to be summed. Below, I have chosen the column with the sales amounts.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumif-google-sheets-date-sum-range.webp” alt=”SUMIF date range in Google Sheets \u2014 sales_amount column E:E selected as the sum range” title_text=”Sales_amount column E:E selected as the sum range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
5.<\/strong> That\u2019s it. The total amount for sales made on or after February 1, 2022, is $332,194.88. The full formula is =SUMIF(A:A,">=2\/1\/22",E:E).<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumif-google-sheets-date-result.webp” alt=”SUMIF date range result in Google Sheets \u2014 $332,194.88 in sales after Feb. 1 in cell G5″ title_text=”$332,194.88 in sales after Feb. 1 in cell G5″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
More SUMIF examples: greater than 0, partial text, and not equal<\/h2>\n
Each of these formulas uses one condition. Swap in your own ranges.<\/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
SUMIF greater than 0<\/h3>\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 SUMIF<\/b> function can sum values that are greater than zero.<\/p>\n
This is particularly useful for financial data, where you may want to exclude negative balances or zero values from your totals.<\/p>\n
The example below the function sums all values in A1:A10 that are greater than 0.<\/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”)<\/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
SUMIF contains partial text<\/h3>\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 SUMIF<\/b> function can also be used to sum values based on text criteria. You can even include wildcards to consider partial matches.<\/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.<\/p>\n
The example below sums the values in B1:B10 where the matching cells in A1:A10 contain the substring “text”.<\/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*”, B1:B10)<\/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
SUMIF does not equal<\/h3>\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.<\/p>\n
The SUMIF<\/b> function can sum values that do not equal a criterion, which is great to filter out unwanted data.<\/p>\n
The example below sums the values in A1:A10 where the cells do not equal 5.<\/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”)<\/p>\n
[\/et_pb_text][et_pb_text module_id=”sumif-vs-sumifs-when-you-need-more-than” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SUMIF vs SUMIFS: when you need more than one condition<\/h2>\n
SUMIF checks one condition. When a total depends on two or more, such as one product sold after a certain date, use SUMIFS.<\/p>\n
The two functions take their arguments in a different order, and that’s where most mistakes happen:<\/p>\n
\n
- =SUMIF(range, criterion, [sum_range]): the range to add up comes last, and you can leave it out.<\/li>\n
- =SUMIFS(sum_range, criteria_range1, criterion1, …): the range to add up comes first, and you can’t leave it out.<\/li>\n<\/ul>\n
SUMIFS only adds a row when every condition is true. To add rows that match one condition or another, add two SUMIF formulas together, for example =SUMIF(C:C,”productA”,E:E)+SUMIF(C:C,”productC”,E:E).<\/p>\n
For more cases, see our guide to SUMIFS with multiple criteria<\/a>.<\/p>\n
[\/et_pb_text][et_pb_text module_id=”sumifs-examples-with-multiple-criteria” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SUMIFS examples with multiple criteria<\/h2>\n
The SUMIFS function lets you sum values based on multiple criteria, which can be applied to multiple ranges. For the sake of simplicity, the examples below are based on the same dataset as the one used for SUMIF.<\/p>\n
SUMIFS with criteria on different columns<\/h3>\n
For this example, I want to sum the amounts for all sales of productA made on or after February 1, 2022.<\/p>\n
1.<\/strong> In an empty cell, type the SUMIFS formula.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumifs-google-sheets-type-formula.webp” alt=”SUMIFS in Google Sheets \u2014 =SUMIFS( typed in cell G2 with sum_range highlighted in the help box” title_text=”=SUMIFS( typed in cell G2 with sum_range highlighted in the help box” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
2.<\/strong> Select the range containing the values you want to sum.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumifs-google-sheets-select-sum-range.webp” alt=”SUMIFS in Google Sheets \u2014 sales_amount column E:E selected as the sum range” title_text=”Sales_amount column E:E selected as the sum range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
3.<\/strong> Select the first range to which you want to apply the first criterion. In this case, it\u2019s different from the sum range, as I want to check the product column.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumifs-google-sheets-first-criteria-range.webp” alt=”SUMIFS in Google Sheets \u2014 product column C:C selected as the first criteria range” title_text=”Product column C:C selected as the first criteria range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
4.<\/strong> Add the criterion you want to apply to the first range. In this example, I want an exact match for \u201cproductA\u201d.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumifs-google-sheets-first-criterion.webp” alt=”SUMIFS in Google Sheets \u2014 productA typed as the first criterion” title_text=”ProductA typed as the first criterion” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
5.<\/strong> Next, select the second range. This is the range to which you want to apply the second criterion. Below, I have selected the date column.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumifs-google-sheets-second-criteria-range.webp” alt=”SUMIFS in Google Sheets \u2014 date column A:A selected as the second criteria range” title_text=”Date column A:A selected as the second criteria range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
6.<\/strong> Add the second criterion, which will be applied to the second range. For this example, I have specified that the date should be on or after February 1, 2022.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumifs-google-sheets-second-criterion.webp” alt=”SUMIFS in Google Sheets \u2014 on or after 2\/1\/22 typed as the second criterion” title_text=”On or after 2\/1\/22 typed as the second criterion” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
7.<\/strong> That\u2019s it. Sales of productA made on or after the selected date total $70,811.22. The full formula is =SUMIFS(E:E,C:C,"productA",A:A,">=2\/1\/22").<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumifs-google-sheets-two-columns-result.webp” alt=”SUMIFS result in Google Sheets \u2014 $70,811.22 for productA sales after Feb. 1″ title_text=”$70,811.22 for productA sales after Feb. 1″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SUMIFS with two criteria on the same column<\/h3>\n
SUMIFS can also be used by applying multiple criteria to a single range. Let\u2019s see how you can find the total sales amount for sales with a value between $500 and $1000.<\/p>\n
1.<\/strong> Type the SUMIFS formula into an empty cell.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumifs-google-sheets-one-column-formula.webp” alt=”SUMIFS with two criteria on one column \u2014 =SUMIFS( typed under total sum of sales between $500 and $1000″ title_text=”=SUMIFS( typed under total sum of sales between $500 and $1000″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
2.<\/strong> Select the range of cells containing the values you want to add.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumifs-google-sheets-one-column-sum-range.webp” alt=”SUMIFS with two criteria on one column \u2014 sales_amount column E:E selected as the sum range” title_text=”Sales_amount column E:E selected as the sum range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
3.<\/strong> Select the range to which you want to apply the first criterion. In this case, it\u2019s the same as the sum range.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumifs-google-sheets-one-column-first-range.webp” alt=”SUMIFS with two criteria on one column \u2014 E:E selected again as the first criteria range” title_text=”E:E selected again as the first criteria range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
4.<\/strong> Add the first criterion. In this example, I want to check that values are greater than or equal to $500.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumifs-google-sheets-at-least-500.webp” alt=”SUMIFS with two criteria on one column \u2014 at least 500 typed as the first criterion” title_text=”At least 500 typed as the first criterion” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
5.<\/strong> Select the range you want to check using the second criterion. In this case, it\u2019s the same as the sum range and the first range.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumifs-google-sheets-one-column-second-range.webp” alt=”SUMIFS with two criteria on one column \u2014 E:E selected as the second criteria range” title_text=”E:E selected as the second criteria range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
6.<\/strong> Add the second criterion. Below, I have specified that values should be less than or equal to $1000.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumifs-google-sheets-at-most-1000.webp” alt=”SUMIFS with two criteria on one column \u2014 at most 1000 typed as the second criterion” title_text=”At most 1000 typed as the second criterion” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
7.<\/strong> That\u2019s it. You have the total based on values between $500 and $1000: $98,510.33. The full formula is =SUMIFS(E:E,E:E,">=500",E:E,"<=1000").<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/11\/sumifs-google-sheets-between-result.webp” alt=”SUMIFS result in Google Sheets \u2014 $98,510.33 in sales between $500 and $1000″ title_text=”$98,510.33 in sales between $500 and $1000″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”common-sumif-and-sumifs-errors” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Common SUMIF and SUMIFS errors<\/h2>\n
When a formula returns an error or a total that looks wrong, check these first:<\/p>\n
\n
- If you turn a SUMIF into a SUMIFS, move the sum range to the front. Left at the end, it returns an error or the wrong total.<\/li>\n
- In SUMIFS, every criteria range needs the same number of rows and columns as the sum range. If one is shorter, you get #VALUE!.<\/li>\n
- Operators go inside quotes: write “>5”, not >5. To compare with a cell, join the operator to it, as in “>=”&B1.<\/li>\n
- Dates typed into the criterion, like “>=2\/1\/22”, are read in the spreadsheet’s locale. “>=”&DATE(2022,2,1) works in any locale.<\/li>\n
- SUMIF and SUMIFS skip numbers stored as text, so the total comes out low. Re-enter them as numbers, or convert them with VALUE in a helper column.<\/li>\n<\/ul>\n
[\/et_pb_text][et_pb_text module_id=”conclusion” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Conclusion<\/h2>\n
SUMIF and SUMIFS are versatile and easy-to-use functions. You can add values based on single or multiple criteria, and these criteria can be applied to single or multiple ranges. You now know the syntax of both functions and how to build criteria using comparison and logical operators on numeric and text values. You also have examples of various scenarios using both functions, with step-by-step instructions.<\/p>\n
To learn more about the related functions COUNTIF and COUNTIFS, check out our guide on COUNTIF & COUNTIFS in Google Sheets: formula & examples<\/a>.<\/p>\n
To count matching cells instead of adding them up, see how to use COUNTIF for values greater than 0<\/a>. For formulas that return a value based on a condition, read our guide to IF functions in Google Sheets<\/a>. For more ways to explore and summarize your numbers, see our guide to data analysis in Google Sheets<\/a>.<\/p>\n
If the numbers you add up live in several spreadsheets, Sheetgo can bring them into one place and keep them updated automatically, so your SUMIF totals stay current and your data is ready for AI analysis.<\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
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. This guide will walk you through the syntax of the SUMIF function, provide practical use cases, and introduce the SUMIFS […]<\/p>\n","protected":false},"author":33,"featured_media":44470,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-50065","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"permalink_manager":null,"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/50065","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/users\/33"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/comments?post=50065"}],"version-history":[{"count":1,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/50065\/revisions"}],"predecessor-version":[{"id":266769,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/50065\/revisions\/266769"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media\/44470"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media?parent=50065"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/categories?post=50065"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/tags?post=50065"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}