OmniDocHow to use Excel multi-condition summation SUMIFS: Summarize by multiple conditions
Use the SUMIFS function to summarize data according to two or more conditions, explain parameter order, condition writing, usage of dates and wildcards, and provide common error checking methods, suitable for sales, finance and ledger statistics.
Key points of this article
- The first parameter of SUMIFS is the area to be summed, and every two parameters after that are a group, namely the condition area and the condition. The order cannot be reversed.
- All condition ranges and summation ranges must have the same number of rows, otherwise the formula will return incorrect results.
- The condition is written in the cell and uses an absolute reference. When the formula is copied to another location, the condition will not move with it.
- Dates and conditions greater than or less than must be written in the ">="& cell format. Directly writing date text can easily fail to match.
- Text numbers and extra spaces are common causes of 0 results, check the data format before aggregating.
When summarizing in Excel, many people first use SUMIF to sum based on one condition, but in actual work, several conditions must be met at the same time, such as "Total sales of East China, Product A, and March." If you manually filter and then look at the status bar, you will have to redo the data as soon as it is updated, and it is easy to miss rows. At this time SUMIFS is the more direct method.
This article is written for readers who often deal with sales ledgers, expense details, and inventory records. After reading, you can write SUMIFS formulas with two or more conditions, know how to write conditions, how to compare dates, and you can also find out the reasons yourself when the results are wrong. The example in this article uses a 12-line sales form, which you can replicate in your own form.
How to use SUMIFS to do multi-condition summation? One sentence conclusion
Use the SUMIFS function. The first parameter is the summation area, and each subsequent two parameters are a group, followed by "conditional area" and "condition". Repeat as many groups as needed. The basic form of the formula is =SUMIFS(求和区域, 条件区域 1, 条件 1, 条件区域 2, 条件 2).
| your situation | Recommended practices | reason |
|---|---|---|
| There is only one condition | Use SUMIF, or directly use SUMIFS to write a set of conditions | The structure is simpler and SUMIFS can completely replace it |
| Two or more conditions, both "equal to" | Use SUMIFS to directly write text or reference cells in conditions | This is the standard usage of SUMIFS |
| The condition is a date range or numerical size | Use SUMIFS and write the condition as ">="& cell | Comparison operators need to be concatenated into text to be recognized |
| There is an "or" relationship between conditions | Use SUMIFS for each condition separately and add them together, or use SUMPRODUCT instead | Multiple conditions of SUMIFS default to an "and" relationship |
| Need to retrieve the value of a certain record based on the number | Use a search function instead and sum the results | SUMIFS is a summary and is not responsible for searching |
Before you start: Confirm these points first
1. Whether the data is in a regular table. Each column contains the same type of information, the first row is the title, and there are no blank rows or merged cells in the middle. If there are merged cells or blank rows in the table, the range references will be misplaced, and the results may appear normal but are actually missed.
2. Numbers are not real numbers. Amounts copied from web pages or PDFs are often in text format and left aligned, and SUMIFS will treat them as 0. You can quickly judge by selecting a column and seeing if the status bar displays the summed value.
3. Whether the condition text is consistent. "East China" and "East China" (with an extra space at the end) are two different values in Excel. Filtering the duplicate items that can be seen in the drop-down list is often a problem of this kind.
4. Whether the data source is PDF. If the sales table is a PDF file, you can first use the desktop client's PDF editor to do OCR (if it is a scan), then export it to Excel, and then write the formula. After exporting, you still need to check the numbers against the original file.
5. Whether the number of rows in the condition area and the summation area are the same. For example, if the summation area is D2:D13, the condition area must also be 12 lines. If it is written as A2:A12, an error will be reported directly or the wrong result will be obtained.
Steps to use SUMIFS to summarize sales of "East China Region, Product A"
Assume that column A of the table is the region, column B is the product, column C is the month, column D is the amount, and the data is in rows 2 to 13. The goal is to calculate the total amount of product A in East China.
- Enter
=SUMIFS(in a blank cell. This step is just the beginning of writing the function, not calculation yet. - The first parameter selects the summing area, that is,
D2:D13, which means that the amount column is to be summed. - Select the first condition area
A2:A13for the second parameter, write the condition"华东"for the third parameter, and add English double quotes for the text condition. - The fourth parameter selects the second condition area
B2:B13, and the fifth parameter writes the condition"产品 A". - Enter the right bracket and press Enter. The complete formula is
=SUMIFS(D2:D13,A2:A13,"华东",B2:B13,"产品 A"), and the cell will display the total value. - If you want to change the conditions frequently, write "East China" and "Product A" in cells F2 and F3 respectively, and change the formula to
=SUMIFS(D2:D13,A2:A13,F2,B2:B13,F3). You can just change the cell content without changing the formula. - When copying the formula to other locations, change the summation area and condition area to absolute references, such as
$D$2:$D$13,$A$2:$A$13, to prevent the reference range from shifting with copying. - When you need to count data after a certain date, write the condition as
">="&F4and put the date in F4. Comparison operators must be concatenated with quotation marks. Directly written in the form of>=2024/1/1cannot match in some cases.
Check each item after completion
After calculating the results of the formula, do not use it directly. First check it item by item according to the table below.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Is the total reasonable? | Use the filter function to select the same rows and look at the summed value in the status bar. | When the two are inconsistent, it is probably because the condition text is not consistent or the number of rows in the area is incorrect. Recheck each parameter of the formula. |
| Whether the result is 0 | The result is 0 but there is clearly data in the table | Check whether the amount column is a text number. After selecting it, use "Convert to Number" or multiply by 1 to correct it. |
| Whether the condition matches all data | How to compare conditional cells with source data | Use the TRIM function to remove excess spaces and unify full-width and half-width characters |
| Does wildcard take effect? | When an asterisk or question mark is used in the condition | The asterisk represents any number of characters, and the question mark represents one character; if you want to match a real asterisk or question mark, you need to add a tilde in front of it. |
| Is the copied formula misplaced? | Check the reference range of each cell after dragging and copying | Add the $ sign to the summation area and condition area to lock the range |
Before summarizing important data, it is recommended to extract a few lines for manual verification. SUMIFS itself is an accurate calculation, but when the data format and conditional writing are wrong, it will faithfully give wrong results.
📊 Core comparison
There are three common ways to write multi-condition sum in Excel, each of which is suitable for different scenarios.
| method | Suitable situation | Things to note |
|---|---|---|
| SUMIF | Sum only by one condition | Only one set of condition areas and conditions can be set. If you want to add a second condition, you must change it to SUMIFS. |
| SUMIFS | Multiple conditions are met simultaneously (and are related) | The number of rows in the condition area must be consistent, which is the first choice for multi-condition summation. |
| SUMPRODUCT | Condition contains OR, requires multiplication, or more flexible logic | The formula is long and suitable for complex situations. It is not necessary to be the first choice for daily summary. |
Most of the daily ledger statistics can be completed using SUMIFS only. Only when there is an "OR" relationship between the conditions, you need to consider SUMPRODUCT or sum them up separately and then add them together.
❓ Frequently Asked Questions (FAQ)
1. What is the difference between SUMIFS and COUNTIFS?
SUMIFS is the sum of the values that meet the conditions, and COUNTIFS is the count of the number of rows that meet the conditions. The conditions of both are written in the same way, except that COUNTIFS does not have a summation area parameter. If you need to count "how many orders have been placed for product A in East China", just use COUNTIFS.
2. Can SUMIFS find data based on multiple conditions?
SUMIFS only summarizes and is not responsible for returning the content of a certain record. If you want to find the corresponding name or unit price based on the number, you should use the search function and combine it with other functions to process the results.
3. How to write the condition as "not equal to a certain value"?
Just write "<>华东" in the condition, which means it is not equal to the row in East China. Note that quotation marks and symbols must be entered in English half-width mode, otherwise the formula may report an error.
4. Why is the result still 0 even though my formula is written correctly?
The common reason is that the amount column contains text numbers, or the condition text contains hidden spaces. Checking the format of the amount column first, and then using the TRIM function to process the column where the condition is located usually solves the problem.
5. The data is exported from PDF, can I still use SUMIFS?
Can. After exporting to an Excel table, SUMIFS can be used like a normal table. However, the exported format may not be completely regular. It is recommended to check the numbers and spaces first, and compare several key numbers with the original PDF text.
📝 Summary
The core of SUMIFS is "summation area first, condition area and condition arranged in pairs". As long as you remember the order of parameters, refer to conditional cells absolutely, and check the number format, multi-condition summarization can be completed stably. For situations where an OR relationship or more complex logic is required, consider alternatives such as SUMPRODUCT.
