Do you need to sum the values in a range of cells based on specific criteria? If so, you can use the SUMIFS function in Excel. This function is very versatile and can be used to calculate totals for a wide range of scenarios. In this article, we will show you how to use SUMIFS in Excel and provide some examples of how it can be used.

This can be very useful when you need to sum values based on multiple criteria. In addition, the SUMIFS function can be used to sum values based on a date range. This can be very useful if you need to calculate totals for a specific period of time The SUMIFS function is very easy to use and it can save you a lot of time when calculating totals in Excel.

If you are not familiar with how to use SUMIFS in Excel, we highly recommend that you take a look at our how-to guide.

How to Use SUMIFS in Excel:The grammatical structure of the SUMIFS function

=SUMIFS(sum_range, criteria_range_one, criteria_one, [criteria_range_two], [criteria_two], …)

The sum_range argument is the range of cells that you want to sum. The criteria_range_one and criteria_one arguments are used to specify the first criterion that you want to use. The optional criteria_range_two and criteria_two arguments are used to specify a second criterion. You can use up to 128 criteria in the SUMIFS function.

How to Use SUMIFS in Excel: Example

1. Open the data file you want to sum on the based the different criteria.

Suppose we have the data of sale to different countries and in different month and we want to calculate the sale of China but the other condition is only that sale which is more than 20000 in the month of January.

2. As you open the file go to the formulas and select insert function. The insert function window will open.

3. Once you have the insert function window select the sumifs function and then click OK.

4. As you hit ok the function argument window will open select the area in which is data you want to sum in sum range.

5. Range 1 is the list of countries because we want to calculate the sale of China once the list of countries is selected the criteria for first range will be “CHINA”.

6. Now select the range 2 the area in which the sale is which we want to calculate as shown in picture.

7. Now the put the value in criteria 2 >20000 it means we have the second condition which is the sale should be more than 20000 and then hit ok.

8. As you hit ok you will get the result.

How to Use SUMIFS in Excel: difference between sumif and sumifs

The SUMIF function can only be used to sum values based on a single criterion. The SUMIFS function can be used to sum values based on multiple criteria. This makes the SUMIFS function much more versatile than the SUMIF function.

We hope this article has helped you learn how to use the SUMIFS function in Excel. If you have any questions, please feel free to leave a comment below. Should you need more details about the WPS office, please visit the WPS Academy website. You may also download WPS Office for free from this page and use it to edit documents in the Word, Excel, PowerPoint, and PDF formats.

Trustpilot4.8

WPS Office- Free All-in-One Office Suite

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

Free Download

5,820,008 User

Algirdas Jasaitis

Why SUMIF Not Working in Excel

If you encounter issues with the SUMIF function not working in Excel, one possible reason could be an uneven data format. The SUMIF function is designed to work with numbers that can be summed up. Here's a simple solution to address this problem:

1. Check the sum range: Ensure that the range of cells you are summing up is in the proper number format. Inconsistent formatting, such as mixed text and numbers, can cause errors in the SUMIF function.

2. Verify data format during import: When importing data from external sources into Excel, it's common to encounter uneven data formats. Take extra care to review the data during the import process and ensure that it is consistent and in the desired number format.

By following these steps, you can address the issue of the SUMIF function not working due to an uneven data format. It's crucial to maintain consistent formatting in your data to ensure accurate calculations and reliable results with the SUMIF function in Excel.

FAQs about the Sumifs Function in Excel

Q1. How to prevent errors when using SUMIFS function?

To prevent errors, use error handling functions like IFERROR and IFNA around your SUMIFS formula. These functions allow you to handle errors gracefully by specifying fallback values or alternative calculations.

Q2. What is the advantage of using SUMIFS function in Excel?

Utilizing the SUMIFS function has the benefit of allowing conditional summing to be done in a single formula. Based on predetermined conditions, it speeds up computations, simplifies calculations, and produces correct results.

Q3. What is another purpose of using SUMIFS function?

The SUMIFS function has other uses besides adding up a group of cells: it can also compute averages. You can count instances that satisfy the criteria by combining SUMIFS and COUNTIFS, and you may divide the sum by the count to get an average.

Q4. How does SUMIFS function handle updating data?

Based on additional criteria or modifications to the data, the SUMIFS function automatically recalculates the sum. Due to its dynamic behavior, data upkeep is made easier and real-time calculations are provided, guaranteeing that your results are always accurate without the need for human modifications.

Summary

This article gave a thorough explanation of how to use Excel's Sumifs function effectively. It addressed a wide range of techniques, such as detailed instructions, the use of single and multiple criteria, cell referencing, and logical operators. You can boost your productivity and your Excel skills by understanding the Sumifs function. Consider using WPS Office, a strong Excel substitute, for more information and examples.