+ Reply to Thread
Results 1 to 4 of 4

Sumproduct(Sumifs with 2 named ranges

  1. #1
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852

    Sumproduct(Sumifs with 2 named ranges

    Hi, I want to SUMIF the totals if the values are in the combination of 2 named ranges. Usually, for 1 named range I use the =SUMPRODUCT(SUMIF formula and it works fine.
    Now that I have 2 named ranges I tried the SUMPRODUCT(SUMIFS formula without success.

    Any ideas? See attached the test sheet with 2 named ranges + the desired result.
    In red the values I want to be included in the sumif but are not being considered in the formula.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Sumproduct(Sumifs with 2 named ranges

    Array formula:

    =SUM(SUMIFS(C2:C11,A2:A11,RANGE_1,B2:B11,TRANSPOSE(RANGE_2)))
    Rory

  3. #3
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852

    Re: Sumproduct(Sumifs with 2 named ranges

    Great! Love learning new excel skills. Thanks

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Sumproduct(Sumifs with 2 named ranges

    Glad to help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sumproduct with multiple named ranges
    By NYmerc in forum Excel General
    Replies: 4
    Last Post: 06-06-2018, 04:41 PM
  2. [SOLVED] SUMPRODUCT with named dynamic ranges
    By Juolupuki in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-21-2017, 06:19 AM
  3. Weighted average based on various date ranges (SUMIFS or SUMPRODUCT)?
    By MajorMattMason in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 08-14-2014, 09:34 PM
  4. SUMIFS or SUMPRODUCT with multiple criteria including date ranges
    By baxcat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-25-2013, 09:40 AM
  5. [SOLVED] Multiple SumIFs with named table ranges
    By Cerenity in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-25-2013, 11:27 AM
  6. SUMPRODUCT and named ranges
    By clayton in forum Excel General
    Replies: 3
    Last Post: 11-02-2006, 09:08 PM
  7. sumproduct using array of named ranges
    By Exceler in forum Excel General
    Replies: 1
    Last Post: 08-27-2006, 12:35 PM

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