+ Reply to Thread
Results 1 to 9 of 9

Can you nest multiple SUMIFS into a formula?

  1. #1
    Registered User
    Join Date
    12-30-2010
    Location
    somewhere, USA
    MS-Off Ver
    Excel 2010
    Posts
    86

    Can you nest multiple SUMIFS into a formula?

    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.

  2. #2
    Valued Forum Contributor khamilton's Avatar
    Join Date
    10-08-2009
    Location
    IL
    MS-Off Ver
    Excel 2007
    Posts
    345

    Re: Can you nest multiple SUMIFS into a formula?

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

    http://www.officearticles.com/excel/...soft_excel.htm
    Please acknowledge the response you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

  3. #3
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Can you nest multiple SUMIFS into a formula?

    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(...), ..., ...) ).
    Last edited by Whizbang; 10-28-2011 at 03:02 PM.

  4. #4
    Registered User
    Join Date
    12-30-2010
    Location
    somewhere, USA
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: Can you nest multiple SUMIFS into a formula?

    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.

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: Can you nest multiple SUMIFS into a formula?

    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))
    Last edited by jeffreybrown; 10-28-2011 at 07:13 PM.
    HTH
    Regards, Jeff

  6. #6
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Can you nest multiple SUMIFS into a formula?

    You can have multiple criteria on a single range.

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

  7. #7
    Registered User
    Join Date
    09-22-2011
    Location
    Washington, USA
    MS-Off Ver
    Excel 2003,2007, 2010
    Posts
    85

    Re: Can you nest multiple SUMIFS into a formula?

    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!
    Attached Files Attached Files

  8. #8
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Can you nest multiple SUMIFS into a formula?

    Wasn't this covered in your previous question here?

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

  9. #9
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Can you nest multiple SUMIFS into a formula?

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1