Sumproduct alternative

1. 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. 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. 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)

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. 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. 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. cheers Barry... I always thought it applied to both... ach well... wrong again.

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

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