Tuesday, September 18, 2012

yogi_Sum Values By Filtering A Column Based On Value Matching Any Value In Another Column

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #758   Sep 18, 2012     www.energyefficientbuild.com.

user unreasonableman said: (http://productforums.google.com/forum/?zx=moicavc8fpfc#!category-topic/docs/spreadsheets/JfVl8gJGA_4)
Filter a column based on value matching any value in another column 
I'd like to sum all values in column B where the cell in column A matches any of the values in column Z.  For example:
         A                           B                                      
5 gal keg                        5                                               
fire 5 gal keg                  1                                               
keg of beer                    2                        
fire keg                          3                                               
5 gal keg beer                5                        
beer 5 gal keg                6                       
12/12oz beer                  0
keg of beer                    2                        
64 oz beer                   24
Column Z
keg of beer
beer keg
beer 5 gal keg
5 gallon keg beer
This is what I hoped would work:
= iferror( arrayformula( sum( filter( B2:B ; A2:A=Z2:Z ) ) ) )
---------------------------------------------------------------------------------------------
following is a solution to the problem