Thursday, April 25, 2013

yogi_Compute Sum Of Numbers In Column C For Numbers In Column B That Are Contained In String In A1

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 25, 2013
Nifty way to do lookups and sum with input as a string?
Hi!

Im trying to create a smart formula for doing some lookpus. If i could do a FOR loop or such, i would have no problem, but im kind of new to thinking in spreadsheet formulas...

My input is non-negative integers separated by commas, given in a cell. Say A1 holds that input. for example "200, 234, 1919, 31919".
In column  B and C i have key-value pairs. Keys are integers and values are floats.

Now i want to sum the values in column C corresponding to the keys in B that occurs in the input A1.

I tried this:
=ArrayFormula(sum(not(iserr(find(B2:B,A1)))*C2:C))

it works quite nice, but if the key "200" occurs, and i have the input "2000", it will contribute to the sum because of the find-method behaviour. Is there any good workaround?
---
oh.
and here is an example sheet!
------------------------------------------------------------------------------------------------------------------
subsequent to a lively discussion in Google Docs User Forum in the following I present Hyde/Ludvig formula
which works for the case in the original post and some alternate solutions for a bit more generalized case