• 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.

Sumproduct or Sumif Vlookup combo?

E

ecgilboy

New Member
Hi Excel Gurus,

Need some help with formula,I am stuck on what function to use in D3:D17. Formula to include multiplying two(2) cells if condition is met..I guess SUMPRODUCT with combination of VLOOKUP or INDEX/MATCH will do. But I'm still struggling with Excel functions/formula....I have attached excel file with explanation of what result I'm trying to achieve....

Pls. Help

ecgilboy
 

Attachments

Hi,
I think you can go with SUMPRODUCT:
=SUMPRODUCT(($I$3:$I$10=B3)*($J$3:$J$10)*C3)

or with this CSE:
=SUM(IF($I$3:$I$10=B3,$J$3:$J$10*C3))
Enter with Ctrl+Shift+Enter

Regards,
 
Thankfully you have setup a good lookup table, so you can also go with simple lookup formula:

=LOOKUP(B3,$I$3:$I$10,$J$3:$J$10)*C3
 
I don't believe the LOOKUP version will work in all cases, because for LOOKUP to work as required the lookup range (in this case I3:I10) should be sorted ascending, which it isn't here. That means you get the wrong result in D10 because LOOKUP returns the wrong result for FDR 7.

Even if the table was to be sorted, LOOKUP is probably not advisable because it might give you a match when you don't want one, e.g. what would be the result if the lookup value is FDR 5 when that value doesn't appear in I3:I10?

I'd suggest using a simple VLOOKUP like this:

=VLOOKUP(B3,I$3:J$10,2,0)*C3

Using that formula the ordering of the table is irrelevant.....and if B3 doesn't exist in the lookup range I3:I10 you will get an error (#N/A)

regards, barry
 
Hi Barry,

Appreciate for looking and responding, and thank you for the formula...I like to get different approach to the solution coz it help me a lot in understanding excel functions...

ecgilboy
 
@ ecgilboy,

Just for fun!!

As u are understanding excel functions so that these will also play the same roles!!

=INDEX($J$3:$J$10,MATCH(B3,$I$3:$I$10,0))*C3
=SUMIF($I$3:$I$10,B3,$J$3:$J$10)*C3
=VLOOKUP(B3,$I$3:$J$10,2,0)*C3
 
Hi Deepak,

Thank You very much for looking and for another variation of the solution...you guys rocks...I'm overwhelmed on the response on my requested formula...

ecgilboy
 
You must log in or register to reply here.
Back
Top