Can you nest multiple SUMIFS into a formula?

**DorothyFan1** · 10-27-2011, 08:11 PM

I'm curious to know if it's possible to nest multiple SUMIFS in one formula. If so how would this be constructed? Thanks in advance.

**khamilton** · 10-27-2011, 11:34 PM

Check this out once and see if it helps you!!!

http://www.officearticles.com/excel/...soft_excel.htm

**Whizbang** · 10-28-2011, 10:14 AM

SUMIF and SUMIFS merely return a number. So, you can place the SUMIF/SUMIFS function in any other fucntion's parameter that expects a number, including other SUMIFS. I imagine you would most often use it to compare values (e.g. IF(B1>= SUMIFS(...), ..., ...) ).

**DorothyFan1** · 10-28-2011, 06:59 PM

The only reason I ask is because I was wondering if it's possible to use SUMIFS in a way it wasn't intended. Like being able to use multiple criteria from *one* range.

**jeffreybrown** · 10-28-2011, 07:10 PM

Did you try it or better yet, post the formula you are trying with an explanation and we might be able to help?

One possible solution...

=SUMPRODUCT(SUMIF(A1:A10,{"Apple","Orange"},B1:B10))

**Whizbang** · 10-28-2011, 07:26 PM

You can have multiple criteria on a single range.

=SUMIFS(A:A,B:B,">=" & DateValue("01/01/2011"),B:B, "<=" & DateValue("12/31/2011")

**pmalen** · 10-29-2011, 11:21 AM

You can use sumif to create multiple criteria and it works for both rows and columns. It is the superpower of Excel data extraction. I use sumif like an addict!

**daddylonglegs** · 10-29-2011, 11:33 AM

Wasn't this covered in your previous question here?

Can you be more specific about what you want to do this time, thanks

**Whizbang** · 10-29-2011, 05:17 PM

From reading the linked post, I assume you are referring to summing bases on an "OR" condition.

E.G. Items in column = "Data 1" or "Data 2"

You can do this using SUMPRODUCT

=SUMPRODUCT((A1:A100="Data 1")+(A1:A100="Data 2"),B1:B100)

See this link for details on SUMPRODUCT
http://xldynamic.com/source/xld.SUMPRODUCT.html