如果您经常使用Google 表格(Google Sheets)，并且需要根据特定单元格中的某些条件将值汇总在一起，那么您需要知道如何使用Google 表格中的(Google Sheets)SUMIF函数。
使用此函数对数据求和的能力不仅限于两个值。您可以对整个范围求和。您为其提供求和函数的条件也取决于电子表格中的多个单元格。
SUMIF 函数(SUMIF Function Works)在Google表格中的工作原理
SUMIF 是一个简单的电子表格功能(simple spreadsheet function)，但它足够灵活，您可以使用它进行一些创造性的计算。
您需要按如下方式构建函数：
SUMIF（范围，标准，[sum_range]）(SUMIF(range, criterion, [sum_range]))
该函数的参数如下：
- Range：您要用于评估是否将值相加的单元格范围。
- Criterion：您要评估的单元格的条件。
- Sum_range：此参数是可选的，包括要求和的单元格。如果您不包含此参数，该函数将简单地总结范围本身。
该函数看起来很简单，但是您可以对多个单元格的范围进行求和或比较这一事实提供了比您可能意识到的更大的灵活性。
带有文本的 SUMIF 示例
如果您已准备好开始使用SUMIF函数，最好的方法是在电子表格中使用两列。一列将用于您的比较，另一列将是您要添加的值。
上面的示例表是在一段时间内跟踪购买的店主的表。店主想要创建额外的列来汇总 B 列中 A 列中特定值的购买价格。
在这种情况下，比较范围为A2:A15。
标准将是要添加的项目的搜索短语。因此，在这种情况下，要将所有 post digger 购买相加，标准将是文本“Post Digger”。
总和范围将是具有要汇总的值的单元格范围。在这里，这是B2:B15。
当您按下回车键时，您将看到 sum_range 列中的特定值相加，但仅包含A 列与您指定的条件匹配的单元格中的数据。(the data)
这是使用SUMIF函数的简单方法；作为一种根据第一列中列出的项目从第二列中提取值的方法。
注意(Note)：您不必在双引号内的公式中输入标准。相反，您可以将该值键入工作表中的单元格，然后将该单元格输入到公式中。
对文本使用 SUMIF 运算符
虽然上面的示例寻找完美匹配，但您也可以使用运算符来指定要匹配的文本部分。如果您修改搜索条件，您可以总结可能不完全匹配但为您提供您正在寻找的答案的单元格的值。
使用上面的示例，如果您想将除(except)电钻之外的所有项目的购买量相加，您将输入公式<>运算符。
=SUMIF(A2:A15,”<>Electric Drill”,B2:B15)
<> 运算符告诉SUMIF函数忽略“电钻”，但将 B2: B15范围内的所有其他项目相加。
从下面的结果可以看出，SUMIF函数正常工作。
对文本使用SUMIF函数时，还可以使用以下运算符：
- ? : 搜索带有您放置 ? 的任何字符的单词。例如，“S?ovel”将对任何以“S”开头和以“ovel”结尾的项目相加，中间有任何字母。
- *：搜索以任何开头或结尾的单词。例如，“*Watch”将添加任何类型的手表的所有项目，无论品牌如何。
注意(Note)：如果您确实希望SUMIF函数搜索“？”之类的字符 或文本中的“*”（不要将它们用作特殊字符），然后在前面加上波浪号。例如，“~？” 将包括“？” 搜索文本中的字符。
请记住，SUMIF函数不区分大小写。因此，当您使用文本作为搜索条件时，它不会区分大写字母或小写字母。这很有用，因为无论是否使用大写字母输入相同的单词，SUMIF函数仍会将其识别为匹配项，并将正确汇总 value 列中的值。
对数字使用 SUMIF 运算符
当然，Google 表格中的(Google Sheets)SUMIF函数不仅可用于在列中查找具有关联值的文本以进行汇总。您还可以对满足特定条件的数字范围求和。
要检查某个条件的数字范围，可以使用一组比较运算符。
- > : 大于
- < : 小于
- >= : 大于等于
- <= : 小于或等于
例如，如果您有一个数字列表并且想要添加超过 3000 个的数字，您可以使用以下SUMIF命令。
=SUMIF(B2:B15, “>3000”)
请注意，就像文本条件一样，您不需要直接在公式中输入数字“3000”。您可以将此数字放入单元格并使用该单元格引用而不是公式中的“3000”。
像这样：
=SUMIF(B2:B15, “>”&C2)
在我们看一个例子之前的最后一点。您还可以对范围内等于特定数字的所有值求和，只需完全不使用任何比较运算符即可。
带数字的 SUMIF 示例
让我们看看如何通过使用带有数字的比较运算符 在Google 表格中使用(Google Sheets)SUMIF函数。
在此示例中，假设您是一名徒步旅行者，追踪您徒步旅行过的所有山峰。
在单元格 D2 中，您想要将您徒步过的 3000 英尺以上的所有山脉的总高度相加。
为此，您需要使用上一节中提到的公式。
输入公式后按Enter，您将在此单元格中看到结果。
如您所见，Google 表格中的(Google Sheets)SUMIF函数总结了任何高于 3000 英尺的山的 B 列的所有海拔高度。SUMIF公式忽略了该高度下的所有值。
使用(Use)上一节中列出的其他条件运算符对小于、大于或等于、小于或等于或等于的数字执行相同的计算。
对日期使用 SUMIF 运算符
您还可以对日期使用SUMIF函数。同样(Again)，上面列出的相同比较运算符也适用，因此您不必担心学习新的比较运算符。
但是，要使该功能正常工作，首先需要在Google 表格(Google Sheets)中正确格式化日期。
您可以手动将日期键入函数或将其键入单元格并将其引用到公式中。其格式如下：
=SUMIF(B2:B15, “>10/4/2019”, C2:C15)
这是如何工作的：
- SUMIF将检查 2019 年 10 月 4 日之后的任何日期的范围 B2：B15。
- 如果为真，SUMIF将汇总 C2:C15 中此比较为真的同一行中的所有单元格。
- 结果总计将显示在您键入公式的单元格中。
如果您有一个单元格的日期未以这种方式格式化，则可以使用DATE函数正确地重新格式化日期。例如，如果您有三个包含年、月和日的单元格（D2、D3 和 D4），则可以使用以下公式。
例如：
=SUMIF(B2:B15, “>”&DATE(D2, D3, D4), C2:C15)
如果您有一个在表格顶部包含最新购买的电子表格，您可以简单地使用TODAY功能来仅总结今天的购买而忽略其余的。
=SUMIF(B2:B15, TODAY())
(SUMIF)Google 表格(Google Sheets)中的SUMIF简单但用途广泛(Versatile)
如您所见，Google Sheets中的(Google Sheets)SUMIF公式并不需要很长时间来学习。但是你可以使用它的各种方式使它如此多才多艺。
如果您使用许多电子表格，需要根据其他单元格中的文本或数字的条件汇总值，您应该熟悉SUMIF函数。
How to Use SUMIF in Google Sheets
If you regularly use Google Sheets and ever need to ѕum valυes together based on some conditiоn in specіfic cells, then you’ll need to know how to use the SUMIF function in Google Sheets.
The ability to sum data with this function isn’t limited to just two values. You can sum an entire range. And the condition you provide the function for it to sum or not can depend on multiple cells in your spreadsheet too.
How the SUMIF Function Works in Google Sheets
SUMIF is a simple spreadsheet function, but it’s flexible enough so that you can do some creative calculations with it.
You need to frame the function as follows:
SUMIF(range, criterion, [sum_range])
The parameters for this function are as follows:
- Range: The range of cells you want to use to evaluate whether or not to add up the values.
- Criterion: The condition of the cells you want to evaluate.
- Sum_range: This parameter is optional, and includes the cells that you want to sum. If you don’t include this parameter, the function will simply sum up the range itself.
The function appears simple, but the fact that you can sum or compare ranges of multiple cells allows for far more flexibility than you may realize.
A SUMIF Example with Text
If you’re ready to get started with the SUMIF function, the best way is to use two columns in your spreadsheet. One column will be for your comparison, and the other will be the values you want to add.
The example sheet above is that of a store owner who’s tracking purchases through a period. The store owner wants to create additional columns that sum up the purchase prices in column B for specific values in column A.
In this case, the range for comparison would be A2:A15.
The criterion would be the search phrase for the item to add up. So, in this case, to add up all of the post digger purchases, the criterion would be the text “Post Digger”.
The sum range would be the range of cells with the values to be summed up. Here, this is B2:B15.
When you press enter, you’ll see the specific values from the sum_range column added up, but only with the data from the cells where column A matches the criterion you specified.
This is a simple way to use the SUMIF function; as a way to pluck values out of a second column based on items listed in the first.
Note: You don’t have to type the criterion into the formula inside double-quotes. Instead, you could type that value into a cell in the sheet and enter that cell into the formula.
Using SUMIF Operators with Text
While the example above looks for perfect matches, you can also use operators to specify parts of the text you want to match. If you modify the search criteria, you can sum up values for cells that may not match perfectly but provide you with the answer you’re looking for.
Using the example above, if you want to add up purchases of all items except the electric drill, you will enter the formula the <> operator.
=SUMIF(A2:A15,”<>Electric Drill”,B2:B15)
The <> operator tells the SUMIF function to ignore “Electric Drill” but add up all other items in the B2:B15 range.
As you can see from the result below, the SUMIF function works as it should.
You can also use the following operators when using the SUMIF function with text:
- ?: Search for words with any character where you’ve placed the ?. For example, “S?ovel” will sum any item starting with “S” and ending with “ovel” with any letter in between.
- *: Search for words that start or end with anything. For example, “*Watch” will add all items that are any kind of watch, regardless of brand.
Note: If you actually want the SUMIF function to search for a character like “?” or “*” in the text (and not use them as special characters), then preface those with the tilde character. For example, “~?” will include the “?” character in the search text.
Keep in mind that the SUMIF function is not case-sensitive. So, it doesn’t differentiate between capital or lower-case letters when you’re using text as the search criteria. This is useful because if the same word is entered with capital letters or without, the SUMIF function will still recognize those as a match and will properly sum up the values in the value column.
Using SUMIF Operators with Numbers
Of course, the SUMIF function in Google Sheets isn’t only useful for finding text in columns with associated values to sum up. You can also sum ranges of numbers that meet certain conditions.
To check a range of numbers for a condition, you can use a set of comparison operators.
- >: Greater than
- <: Less than
- >=: Greater than or equal to
- <=: Less than or equal to
For example, if you have a list of numbers and you’d like to add those over 3000, you would use the following SUMIF command.
=SUMIF(B2:B15, “>3000”)
Note that just like with text criteria, you don’t need to type the number “3000” into the formula directly. You could place this number into a cell and use that cell reference instead of “3000” in the formula.
Like this:
=SUMIF(B2:B15, “>”&C2)
One last note before we look at an example. You can also sum up all values in a range that are equal to a specific number, just by not using any comparison operators at all.
SUMIF Example with Numbers
Let’s look at how you can use the SUMIF function in Google Sheets by using a comparison operator with numbers.
In this example, imagine you’re a hiker tracking all the mountains that you’ve been hiking.
In cell D2, you want to add up the total altitude of all of the mountains over 3000 feet that you’ve hiked.
To do this, you’ll need to use the formula mentioned in the section above.
Press Enter after typing the formula, and you’ll see the results in this cell.
As you can see, the SUMIF function in Google Sheets summed up all altitude heights from column B for any mountain higher than 3000 feet. The SUMIF formula ignored all values under that altitude.
Use the other conditional operators listed in the last section to perform the same calculation for numbers less than, greater than or equal to, less than or equal to, or equal to.
Using SUMIF Operators with Dates
You can also use the SUMIF function with dates. Again, the same comparison operators listed above apply, so you don’t have to worry about learning new ones.
However, for the function to work, dates need to be formatted correctly in Google Sheets first.
You can manually type the date into the function or type it into a cell and reference it into the formula. The format for this is as follows:
=SUMIF(B2:B15, “>10/4/2019”, C2:C15)
How this works:
- SUMIF will check the range B2:B15 for any dates after 10/4/2019.
- If true, SUMIF will sum up any cells in C2:C15 in the same row where this comparison is true.
- The resulting total will be displayed in the cell where you typed the formula.
If you have a cell where the date isn’t formatted this way, you can use the DATE function to reformat the date properly. For example, if you have three cells (D2, D3, and D4) that hold the year, month, and day, you can use the following formula.
For example:
=SUMIF(B2:B15, “>”&DATE(D2, D3, D4), C2:C15)
If you have a spreadsheet that contains the latest purchases at the top of the sheet, you can simply use the TODAY function to only sum up today’s purchases and ignore the rest.
=SUMIF(B2:B15, TODAY())
SUMIF in Google Sheets is Simple But Versatile
As you can see, the SUMIF formula in Google Sheets doesn’t take a long time to learn. But the various ways you can use it make it so versatile.
If you use many spreadsheets where you need to sum up values based on the conditions of text or numbers from other cells, you should familiarize yourself with the SUMIF function.
