One of the most dynamic parts of data analysis is the list of functions in Excel. There is a little bit of something for every kind of user, from fancy math operations to advanced conditional logic. The SUMIFS function is one of many available to users who want to use conditional logic to drive detailed analysis. If you want to know How to Use SUMIFS in MS Excel then don’t worry just follow the steps mentioned below.
Here’s what you need to know about how to use SUMIFS in Excel. The SUMIF function in Excel has been a favourite for a long time, and the SUMIFS function is a great addition to it. It is part of the Maths/Trig group of formulas and can be used to add a range of numbers based on one or more criteria. In simpler terms, SUMIFS works on multiple columns. This is different from the Excel SUMIF function, which could only handle one piece of criteria.
With the SUMIFS function, we can basically SUMIF more than one column. You might use the SUMIFS function in Excel if you work in accounting, finance, or another field where you need to analyse data. This tool lets you choose a range of data and tell it which values you want to add from that range. Visit their official site for more details.
How to Use SUMIFS in MS Excel Via Cell References
- Go to cell F6 and click on it.
- Assign the formula =SUMIFS(C2:C16,A2:A16,F4,B2:B16,F5) to cell F6.
- Press Enter to apply this formula to cell F6.
- This will show the result of $ 4,805, which is how much Pina Colada was sold during the campaign in the state of AL.
How to use SUMIFS in Excel with Comparison Operators
You can compare any type of data with an SUMIFS function by using comparison operators as its arguments. Make sure to put double quotes around the part of the condition that has the operator. To find the total amount of sales in AL that were over $3,000:
- Go to cell F6 and click on it.
- Assign the formula =SUMIFS(C2:C16,B2:B16,”AL”,C2:C16,”>3000″) to cell F6.
- Press Enter to apply this formula to cell F6.
- This will show the result $ 11,250, the sum of the sales in the state of AL over $3,000.
Using SUMIF with Blanks
You can use the SUMIF function with blank cells by giving it an empty string (“”) as a criterion. In the next example, the states have been taken out of the previous data. To find out how much Pina Colada made in sales:
- Go to cell F6.
- Assign the formula =SUMIFS(C2:C16,A2:A16,F4,B2:B16,””) to cell F6.
- Press Enter.
This will give you the total amount of money made from selling pina coladas, which is $14,405. Here, the empty string used as the criteria for cells B2 to B16 adds up the sales for the drinks in A2 to A16 that are linked to the blank cells in column B.
Use SUMIFS with Wildcards
You can use wildcards with SUMIFS to find matches that are close but not exact. You can find matches with the question mark (?) or the asterisk (*).
In cell F6, the formula =SUMIFS(C2:C16,A2:A16,”Mer“,B2:B16,”T?”) gives $ 4,500 as the answer. Here, “Mer” matches the text “Merry Ginne,” and “T?” matches the state “TX,” so the result is the total number of copies of Merry Ginne that were sold in Tx.
How to use SUMIFS with Dates
- Select cell E6.
- Assign the formula =SUMIFS(C2:C16,B2:B16,”IT”,A2:A16,”>=”&TODAY()-2,A2:A16, “<=”&TODAY()) to cell E6.
- Press Enter to assign the formula to cell E6.
- This will show the total work hour 236 for the dept IT in the last two days.
What is the SUMIFS function in Excel?
The SUMIFS function in Excel is a tool that finds specific values in a range of data and adds them together. It’s a more flexible version of the SUMIF function because it lets you choose values based on more than one factor instead of just one. The SUMIFS function is used by many people to quickly calculate the sum of several values in a spreadsheet.
When to use the SUMIFS function
The SUMIFS function is needed when you want to add up a range of data in your spreadsheet based on more than one criteria. This function could also be used when:
Summing only cell ranges: Use the SUMIFS function when you want to add ranges instead of arrays for cells.
For Summing ranges with date criteria: When you use date formats to evaluate your data, the SUMIFS function works with the &DATE function to add up the dates in more than one range.
Summing multiple cell ranges: The SUMIFS is a useful command function for adding up a number of cell ranges and criteria.
FAQ
Can you do a Sumif with 3 criteria?
You can use SUMIFS to make a total based on more than one factor. It does the same thing as SUMIF, but you can set between one and 255 conditions.
What is the purpose of the Sumifs function in Excel?
One of the math and trig functions, SUMIFS, adds all of its arguments that meet more than one condition. For example, you could use SUMIFS to find out how many retailers in the country (1) live in a single zip code and (2) make more than a certain amount of money.
Why is Sumifs not working correctly in Excel?
SUMIF doesn’t work because the data format isn’t consistent. First, you have to make sure that the sum range has the right number format. When importing data from other sources, you may run into different formats.
