[Fixed]: SUMIFS Not Working with Multiple Criteria (3 Solutions)

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, users use the SUMIFS function to determine value by adding arguments that match the given criteria. But sometimes the function may not work when there are multiple criteria in the syntax. This may result in an error or show zero as a result. In this article, I will show you the solution of SUMIFS is not working with multiple criteria in Excel.

3 Suitable Solutions If SUMIFS Is Not Working with Multiple Criteria


SUMIFS Not Working with Multiple Criteria! (3 Suitable Solutions)

In this article, you will see three different scenarios and their solutions regarding the SUMIFS Function not working with multiple criteria in Excel. In the scenarios, I will consider the criteria range, insertion of the proper sign, and correcting the syntax of the function.

For my working purposes, I will use the following sample data set. Here, I have random information regarding the sales of some products.


Solution 1: Giving Proper Criteria Range

While writing the arguments of the SUMIFS function, sometimes users may input improper criteria ranges. Doing this will cause an error in the calculation, and the function will show no actual result. See the solution to this error in the following steps.

Step 1:

  • Firstly, in cell D14 of the data set, insert the following SUMIFS function formula.
=SUMIFS(D5:D10,B5:B12,B14,C5:C12,C14)
  • Here, from the formula, you can see that the proper sum range in the formula is missing.
  • Here, the actual sum range is D5:D12, while in the formula it is D5:D10.

Giving Proper Criteria Range as A Suitable Solution If SUMIFS Is Not Working with Multiple Criteria

Step 2:

  • Secondly, press Enter and you will get #VALUE! as the result.

Step 3:

  • Thirdly, to solve the error, write the following formula by mentioning the proper sum range which is D5:D12.
=SUMIFS(D5:D12,B5:B12,B14,C5:C12,C14)

Giving Proper Criteria Range as A Suitable Solution If SUMIFS Is Not Working with Multiple Criteria

Step 4:

  • Finally, by pressing Enter, you will get the actual result.

Showing Final Result of Giving Proper Criteria Range as A Suitable Solution If SUMIFS Is Not Working with Multiple Criteria

Read More: Excel SUMIFS Not Equal to Multiple Criteria


Solution 2: Inserting Proper Sign

While writing the syntax of the SUMIFS function, you must insert the proper sign whenever it is needed. Otherwise, SUMIFS with multiple criteria will not work. Instead of getting a proper result, you will see an error. To solve this issue, see the following steps.

Step 1:

  • Firstly, in cell D14, we will find out the combined price of the laptop and sound box from the data set.
  • For that, type the following formula in cell D14.
=SUM(SUMIFS(D5:D12,C5:C12,{Laptop,Sound Box}))

Inserting Proper Sign as A Suitable Solution If SUMIFS Is Not Working with Multiple Criteria

Step 2:

  • Secondly, from the above formula, you can see that I have not used any double quotation inside the second bracket.
  • Here, we must input the required symbol because both criteria are in text formats.
  • So, in this case, when you press Enter after typing the above formula, you will get an error message like the following image.

Step 3:

  • Thirdly, to solve the issue, type the formula in the following format by adding a double quotation inside the second bracket.
=SUM(SUMIFS(D5:D12,C5:C12,{"Laptop","Sound Box"}))

Inserting Proper Sign as A Suitable Solution If SUMIFS Is Not Working with Multiple Criteria

Step 4:

  • Finally, after pressing Enter, the function’s formula will provide you with the desired result.

Read More: How to Use SUMIFS When Cells Are Not Equal to Multiple Text


Solution 3: Correcting Syntax Order

As for the third case, sometimes, users forget to write the SUMIFS function’s syntax with multiple criteria in the correct way. So, this results in an error in the calculation. For my last solution to this topic, I will solve this issue in the following steps.

Step 1:

  • First of all, look at the formula of the SUMIFS function in cell D14.
=SUMIFS(C5:C12,B5:B12,B14,D5:D12,C14)
  • Here, in the function syntax, cell range C5:C12 is one of the criteria ranges, not a sum range.
  • Also, the actual sum range of this function is D5:D12, which is mentioned at the end of the syntax.

Correcting Syntax Order as A Suitable Solution If SUMIFS Is Not Working with Multiple Criteria

Step 2:

  • Secondly, pressing Enter with this error will result in a 0 like the following image.

Step 3:

  • Thirdly, correct the syntax of this formula by placing the sum range at the beginning.
=SUMIFS(D5:D12,B5:B12,B14,C5:C12,C14)

Correcting Syntax Order as A Suitable Solution If SUMIFS Is Not Working with Multiple Criteria

Step 4:

  • Finally, by pressing Enter, you will get the actual result.

Showing Final Result Correcting Syntax Order as A Suitable Solution If SUMIFS Is Not Working with Multiple Criteria


Applying SUMIFS Function with Multiple Criteria in Same Column

Additionally, you will see how to use the SUMIFS function for multiple criteria in the same column. Here, I will choose multiple criteria from a single column to get the result. Also, you will observe the use of SUMIFS multiple criteria in both column and row in the same example. So, to learn more about this procedure, follow the following steps.

Step 1:

  • First of all, take the following data set for calculation.
  • Here, I want to know the sales of laptops and sound boxes from column C, which is associated with the salesman Robin from column B.
  • So, from column C, I am selecting more than one criterion.

Step 2:

  • Secondly, insert the following formula in cell D15.
=SUMIFS(D5:D12,B5:B12,C14,C5:C12,B15:C15)
  • Here, I made reference to multiple criteria from both column and row.

Step 3:

  • Finally, after pressing Enter, the formula will show the result of sales for both the laptop and the sound box separately.

Showing Final Result for SUMIFS Multiple Criteria in Same Column

Read More: Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria


Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to solve the issues regarding SUMIFS not working with multiple criteria in Excel. Please share any further queries or recommendations with us in the comments section below.

After commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.


Related Articles


<< Go Back to Excel SUMIFS with Multiple Criteria | Excel SUMIFS Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Araf Bin Jayed
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo