• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using Sum Product Instead of VLOOKUP

I have read that SUMPRODUCT is very powerful and can be used instead of VLOOKUP.

Can someone please guide me or provide a link where I can learn about this technique please?

Thank
 

jeffreyweir

Active Member
VLOOKUP and INDEX/MATCH will be far faster than SUMPRODUCT at looking up an item in a list.

SUMPRODUCT is used for crunching lots of numbers together based on conditions. Yes, it conceivably be used in place of VLOOKUP or INDEX/MATCH. But it should not be.
 

Hui

Excel Ninja
Staff member
VLOOKUP and INDEX/MATCH will be far faster than SUMPRODUCT as they are simply searching or a value based on an index value
Best used when you want to return a single value

Sumproduct is a calculation machine and is great at crunching multiple Ranges/arrays of numbers as well as throwing in conditions for each array
Best used when you want to perform advanced sum's with multiple conditions
Yes it can find a single value but it uses shear grunt to do it
 

jeffreyweir

Active Member
I'd add "Best used when you want to perform advanced sums with multiple conditions and you can't use a PivotTable or the more-efficient SUMIFS"
 

Johnny C

New Member
SUMPRODUCT has the advantage that it can be used on non-contiguous data and will handle conditions on columns and rows at the same time.

I'm of an age that I can remember Excel before SUMIFS when SUMPRODUCT and the magnificent '*--1' were the only way of doing it.
 
Top