+ Reply to Thread
Results 1 to 7 of 7

Sumproduct alternative

  1. #1
    Registered User
    Join Date
    08-13-2004
    Posts
    66

    Sumproduct alternative

    Hello,

    I have a table where I use Sumproduct to look up data in another table using the formula:

    =SUMPRODUCT(($A$100:$A$140=$I9)*($99:$99=$G$2),$100:$140)

    I9 is the ID code and G2 is a country.

    This is fine but will not work if the relevant cell has text or symbols in it. Can anyone suggest an alternative formula?

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528
    not sure I follow -- can you elaborate in terms of "relevant cell"
    can you post an example ?

    also is it a 1:1 relationship between I9 and A1:A140 and G2 to 99:99 ?
    (if so you can use Index/Match)

    finally I'd just make the point that by using 100:140 you're using b-i-g ranges... obviously you're running 2007 (else this wouldn't work) ... assuming you've not reduced number of columns in the sheet (ie default A:XFD) that's around 33000 cells in your sum range, ouch... and if you have more than one of these Sumproducts... :-(

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

    For lookups you are normally better off with a lookup type formula rather than a "summing" type formula.....for the very reason you identified.....and normally because it's more efficient. Try

    =VLOOKUP($I9,$A$100:$IV140,MATCH($G$2,$99:$99,0),0)

  4. #4
    Registered User
    Join Date
    08-13-2004
    Posts
    66
    Quote Originally Posted by daddylonglegs View Post
    As DonkeyOte says...

    For lookups you are normally better off with a lookup type formula rather than a "summing" type formula.....for the very reason you identified.....and normally because it's more efficient. Try

    =VLOOKUP($I9,$A$100:$IV140,MATCH($G$2,$99:$99,0),0)
    Thanks that works perfectly.

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528
    I would still strongly recommend you reduce your range sizes... for ex. in the solution provided the range was switched to A100:IV140 (from 100:140) and this seemingly had no impact on your results... given you're using 2007 this tells us that your original formula was referencing columns needlessly and perhaps IV is still beyond your requirements ? If so reduce the lookup range to a reasonable size and adjust the MATCH accordingly.

    If you don't you're referencing a lot of cells that if/when changed would in turn flag the VLOOKUP calcs as requiring recalculation when in fact they're not really used by the VLOOKUP at all - unlikely in this scenario but a good habit to get into nonetheless... if your data is A100:BZ140 use that range (build in a little spare capacity but not too much).

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

    You're right about range sizes, of course. I should have included something to that effect in my reply.....

    ...but I don't think you can assume that Excel 2007 is being used. SUMPRODUCT can use whole rows even in 2003, the restriction is only on whole columns.

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528
    cheers Barry... I always thought it applied to both... ach well... wrong again.

+ 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