+ Reply to Thread
Results 1 to 5 of 5

SUMPRODUCT with multiple values for criteria?

  1. #1
    Forum Contributor tangcla's Avatar
    Join Date
    06-04-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    136

    SUMPRODUCT with multiple values for criteria?

    I'm lookign at doing a SUMPRODUCT forumula which references items, for example. =SUMPRODUCT((P1:P2000=$A$1)...) where $A$1 is a VLOOKUP'd formula to show FR1, FR2, FR3 etc.

    i'd like to get an option to show "Total" which shows all from FR1 to FR20, for example, is it possible to set A1 to be a value or something which will allow that?

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: SUMPRODUCT with multiple values for criteria?

    It's not immediately obvious as to what you're doing but if I've interpreted correctly, perhaps:

    =SUMPRODUCT(--ISNUMBER(SEARCH($A$1,P1:P2000)))

    If you set A1 such that for ALL you leave A1 blank then the above should return 2000, if A1 is not blank then it should find the appropriate number...

    Note however that the use of SEARCH could lead to issues if you have non mutually-exclusive values, ie if you have FR1,FR10 etc then searching for FR1 would also count instances of FR10 (given FR1 appears within FR10)

    If this is an issue you could use a slightly revised version, eg:

    =SUMPRODUCT(--ISNUMBER(SEARCH(SUBSTITUTE(" "&$A$1&" "," ","")," "&P1:P2000&" ")))
    Last edited by DonkeyOte; 07-27-2009 at 04:50 AM.

  3. #3
    Forum Contributor tangcla's Avatar
    Join Date
    06-04-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    136

    Re: SUMPRODUCT with multiple values for criteria?

    hmm, let's see. FR1 to FR10 are site offices, and I'm trying to get an aggregated sum of dollars of income that each office earns.

    Does that help>

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: SUMPRODUCT with multiple values for criteria?

    I think we'd need more info to help specifically... the latter example should differentiate between the two sites whilst still allowing for criteria (A1) to be blank and thereby returning a total for all sites.

    If you have only one criteria (site) then you may be able to use a SUMIF... but in short we'd need a more detailed explanation with regard to data / ranges, criteria etc...

  5. #5
    Forum Contributor tangcla's Avatar
    Join Date
    06-04-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    136

    Re: SUMPRODUCT with multiple values for criteria?

    It has other criteria, which is built further into the reports but I'll give it a go tomorrow when I'm back at work and I'll see if it works thanks for your help so far!

+ 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