Finding One Result Instead of Multiple

[eforti]

Hello All,
I'm using the below code to return data from a dynamically linked file. The issue I'm running into is that it is only returning data from the first instance of the criteria that it finds. This is how I designed it so that makes sense. But I find that I now have a need to sum the results if multiple instances of the search criteria exist.

Example: The code below is looking for FORD, and if it finds it the value is returned. But if FORD is listed twice in my search range I'd like it to add the resulting values in the returned field.

I feel like this is just changing indirect to sumif, but can't seem to figure out how to get it to work.

=IF($I9="","",IF(ISERROR(VLOOKUP($I9,INDIRECT(CONCATENATE(Reference!$C$6,"$B$4:$O$2000")),5,FALSE)*$V9),0,VLOOKUP($I9,INDIRECT(CONCATENATE(Reference!$C$6,"$B$4:$O$2000")),5,FALSE)*$V9))

Thanks in advance for any help.

 

[GlennUK]

Try using the SUMPRODUCT function for this. Like:

=SUMPRODUCT(--(lookupvalue=listtomatch)*(listofvalues))

 

[eforti]

Glenn,
Thanks for the response. I guess my confusion is stemming from how to integrate the required feature (in this case sumproduct) into my existing code. Again, it is dynamically linking based on other data. I can't do just the sumproduct formula.

 

[GlennUK]

At a guess, maybe:

=SUMPRODUCT(--($I9=INDIRECT(CONCATENATE(Reference!$C$6,"$B$4:$B$2000")))*(INDIRECT(CONCATENATE(Reference!$C$6,"$F$4:$F$2000"))))