Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

How to Use the SUMIFS Function in Excel

The SUMIFS function in Excel sums data that fulfills multiple criteria. It is one of the most widely used math and trig functions to add data based on multiple conditions. In this tutorial, you will learn how to use SUMIFS in Excel.

The SUMIFS Function in EXCEL

Syntax of the Function

SUMIFS(sum_range, criteria_range_1, criteria_1, [criteria_range2, criteria_2], ...)

Arguments within the function

sum_range

Required. The range of cells to sum.

criteria_range_1

Required. The first set of range to be checked.

criteria_1

Required. The value that criteria_range_1 is evaluated against. This defines the cells that will be summed. This can be any number, cell reference, logical expression, text, or another function.

[criteria_range_2, criteria_2,…]

Optional. Additional ranges and their criteria to evaluate. You can add up to 127 range-condition pairs.

Notes About SUMIFS

  • The functions SUMIF and SUMIFS have a lot in common. But SUMIFS can evaluate multiple conditions which is not possible for SUMIF. The first argument for SUMIFS is sum_range, whereas it is the third argument for SUMIF.
  • All other ranges must have the same number of rows and columns as the sum_range.
  • You can use the wildcard characters question mark (?) and asterisk (*) as criteria. A question mark (?) matches one character and an asterisk (*) matches a sequence of characters.
  • You must enclose non-numeric criteria in double quotes (“”). i.e., “>144”. But numeric criteria does not require quotes.
  • To match a question mark (?) or asterisk (*) in a text, use a tilde (~) in front of the question mark or asterisk. i.e., ~?
  • You cannot use SUMIFS with arrays. For this reason, you cannot use functions associated with arrays on the criteria range. For example, you cannot use the YEAR function on criteria_range_1 as it will turn the range into an array. To skip this obstacle, use the SUMPRODUCT function instead.

Examples of the SUMIFS Function

In the following example, you have the sales data for different drinks in three states during a sales campaign.

How to Use SUMIFS in Excel Using Cell References

You can provide cell references as arguments of the SUMIFS function. To find the revenue from the sales of Pina Colada in Alabama (AL):

  • 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 $ 4,805, the sum of the sales of Pina Colada in the state of AL during the campaign.

How to use SUMIFS in Excel with Comparison Operators

You can use comparison operators as the arguments of a SUMIFS function to compare with any data type. Make sure to enclose the condition containing the operator in double quotes. To find the sum of sales in AL 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.

Use SUMIFS with Wildcards

You can use SUMIFS with wildcards characters to find similar but not exact matches. You can use the question mark (?) or asterisk (*) character to find matches.

The formula =SUMIFS(C2:C16,A2:A16,"Mer*",B2:B16,"T?") in cell F6 returns the result $ 4,500. Here, the “Mer*” matches the text Merry Ginne and the “T?” matches with the state TX returning the sum of the sales of Merry Ginne in Tx.

Using SUMIF with Blanks

You can use SUMIF with blank cells using the empty string (“”) provided as a criterion to the SUMIF function. In the following example, we have the previous data with the states being cleared out. To find the total sales for Pina Colada:

  • Go to cell F6.
  • Assign the formula =SUMIFS(C2:C16,A2:A16,F4,B2:B16,"") to cell F6.
  • Press Enter.

This will return the value $ 14,405 which is the total for the sales of Pina colada. Here the empty string provided as the criterion for the cells B2 to B16 sums the sale for the drinks in A2 to A16 associated with the blank cells in column B.

How to use SUMIFS with Dates

You can use the SUMIFS formula in Excel with dates like any other data types. To sum values between two dates, you can use the ampersand (&) operator along with date functions. The following example contains the employee working hours for the last week. To count the total working hour in the past two days:

  • 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.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Are you still looking for help with Conditional Formatting? View our comprehensive round-up of Conditional Formatting tutorials here.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
So im trying to use a sumif formula, but i need it to not look for a range for one specific criteria but instead look for what one specific field says. Example Below =sum(sumifs(QuantityMC,TeamsMC,A6,TypeMC,B2)) The above formula works as on the specific MC sheet an entire column is dedicated for "TypeMC". I want to be able to delete this column and have "TypeMC" to be a single field as it will be the same for every item on that sheet but may differ on a different sheet.
Solved by M. H. in 45 mins
amending a formula so that there is another criteria and if it does not meet this criteria then we stick with the original criteria
Solved by A. B. in 40 mins
Hi I've Col D ( Tech/Non-Tech) & Col J ( contains month name ). Now I want to see the total count of Technical or Non-Technical against the particular month. Can you pls assist with that?
Solved by V. F. in 12 mins
I am looking for a formula to SUM column L, if Column A matched the relevant date and column C matches the name
Solved by Z. Y. in 16 mins
The formula is: =SUMIF(BAARCH!A:R,summary!A6,BAARCH!P:P) which searches for the name inputted on the summary sheet on the BAARCH worksheet - but I want it to look for the name across a range of worksheets. The column is PP.
Solved by Z. C. in 50 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc