Sumif is a function used for single-condition summing in Excel. Its basic structure is =SUMIF (judgment range, condition, summing range). If the judgment is consistent with the summing range, the third parameter can be omitted; common conditions include fixed values ??such as "Zhang San", comparison operations such as ">100", wildcard characters such as "Zhang*" and cell references such as E1; when using it, you need to pay attention to the consistent data type, avoid unnecessary spaces, and write the conditional format correctly; when summing multiple conditions, use the SUMIFS function.

Sumif is a very practical function in Excel, suitable for summing data that meets specific conditions. If you process sales data, inventory records or daily reports frequently, mastering Sumif can save a lot of time.

Basic usage: one condition, one scope

The structure of Sumif is actually very simple, with only three parameters:

 =SUMIF (judging which range, what conditions to judge, which range is actually summed)

For example, if you want to know the sum of sales of "Zhang San", assuming that column A is the name and column B is the sales, then the formula is:

 =SUMIF(A:A, "Zhang San", B:B)

What this means here is: look for "Zhang San" in column A, and then add up the corresponding column B values ??after finding them.

It should be noted that if the "judgment range" and "total range" are the same (for example, you only want to calculate the number greater than 100 in column A), the third parameter can be omitted.

Conditional writing: Flexible but with rules

The second parameter of Sumif is the condition, and several common writing methods include:

• Fixed value: "張三" or 100
• Comparison operation: ">100" or "<>"&A1
• Wildcard characters: "張*" can match "Zhang San", "Zhang Xiaosan", etc.
• Cell reference: E1 , which allows dynamic adjustment of conditions

For example, if you want to count the sum of sales of more than 500, you can write it as:

 =SUMIF(B:B, ">500")

Or if you want to count based on the name in cell E1, change it to:

 =SUMIF(A:A, E1, B:B)

Remember, conditions with comparison symbols should be enclosed in double quotes, and if it is a cell reference, it is not necessary.

FAQ: Why doesn't sumif take effect?

Sometimes when you write a formula, you have no results. There may be several reasons:

• Data types are inconsistent: for example, one is a number and the other is a number in text format
• Extra spaces or hidden characters: especially data imported from outside
• The condition is written incorrectly: Don't mess with the judgment range and the total range
• Conditions are too fuzzy or syntax errors: for example, quotation marks are missing or wrong symbols are incorrect

For example, you copied a name in column A, which looked like "Zhang San", but it was actually "Zhang San" (there was a space behind), and Sumif couldn't find it at this time. You can use TRIM() function to clean it up, or check whether the data is clean first.

Simple advancement: What to do with multiple conditions?

Sumif only supports one condition. If you need multiple conditions, such as the sales of "Zhangsan Shanghai", you have to use the SUMIFS function. Its order is different from Sumif. The first parameter is the summation range, and the following are the conditional range conditions.

But that's another topic, let's talk about Sumif first.

Basically that's it. Once you use it carefully, you will find that it is really common and not difficult, but some details are easy to ignore.

The above is the detailed content of how to use sumif in excel. For more information, please follow other related articles on the PHP Chinese website!