+ Reply to Thread
Results 1 to 3 of 3

SUMPRODUCT using dynamic name range as lookup criteria (name range has multiple values)

  1. #1
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    895

    SUMPRODUCT using dynamic name range as lookup criteria (name range has multiple values)

    Hello,

    I have the following SUMPRODUCT formula and it's working

    Please Login or Register  to view this content.
    However, when I make $B$3:$E$3 as name range, the formula didn't work.

    I tried ways, such as putting double quotation and took out the 'TEXT' function completely and didn't work.

    May I know what did I do wrong? Or SUMPRODUCT can't use name range as a multiple lookup?

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please note that I'm trying to avoid using SUM and SUMIF array ( I know it works). The reason I'm avoiding it is because, it's taking a very long time to calculate. Whereas SUMPRODUCT is almost instant

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: SUMPRODUCT using dynamic name range as lookup criteria (name range has multiple values

    SUMPRODUCT does not work with 2 dimential ranges
    use SUm as an arrayformula

  3. #3
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: SUMPRODUCT using dynamic name range as lookup criteria (name range has multiple values

    deleted by tim
    Last edited by tim201110; 01-20-2017 at 05:05 PM. Reason: double

+ 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. Dynamic Named Range with Unique Values Only for Sumproduct Function
    By tlscowden in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2015, 03:16 PM
  2. [SOLVED] Help with SUMPRODUCT and Match using a Range of values for Lookup
    By Esavoye in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2013, 01:05 PM
  3. Replies: 10
    Last Post: 02-19-2013, 12:05 PM
  4. [SOLVED] Multiple Criteria for Dynamic Range
    By jasperhuang93 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-20-2012, 11:30 AM
  5. Replies: 1
    Last Post: 07-10-2012, 07:39 AM
  6. Sumproduct with multiple criteria & dynamic range
    By e_lad in forum Excel General
    Replies: 10
    Last Post: 03-17-2010, 09:15 AM
  7. [SOLVED] Dynamic range names, multiple criteria, sumproduct
    By goss9394@yahoo.com in forum Excel General
    Replies: 1
    Last Post: 09-19-2005, 10:05 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