Sunday, February 3, 2013

yogi_Sum Up Number Of Guests Invited To Wedding From Different States


                                          Google Spreadsheet   Post  #1008
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 03, 2013
post by user: collin.m.barrett said:(http://productforums.google.com/forum/?zx=fd3vq3iqu592#!category-topic/docs/spreadsheets/MGyKWBU2y8A)
How do I sum a column conditional on another column containing a certain string? 
I have a spreadsheet that I am using to manage a guest list for three separate events. Each row is a different party's information. One column contains the total number of people invited in that party. Another column includes a comma separated list of the events that that party is invited to. How can I write a formula at the bottom of the "number in party" column that sums every value in that column that has a specific corresponding event invite?

Sample records:

4     |     OH
5     |     TN
7     |     OH, TN
1     |     TN
2     |     OH, TN

_______________

Formula for sum of OH?
Formula for sum of TN?
---
See cell A224 in this sample copy of my spreadsheet. Any Ideas?


---------------------------------------------------------------------------------------------------------
I have presented a solution with alternate formulas with a suggested change in layout -- by pulling the totals on top ... this will give you the flexibility of freely adding more guests at the bottom of the list without having to worry about adjusting any of the formulas ... plus you see the number of guests right up front without having to scroll all the way down to the bottom of the list ...