Thursday, September 20, 2012

yogi_Compute Number Of Invited Guests Who Have Accepted Invite By Inviting Party

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

user Ethereal87 said: (http://productforums.google.com/forum/?zx=y7jq6icx61c4#!mydiscussions/docs/zK7amRbbUHM)
Wedding Spreadsheet - Calculate total attending guests per family
So here's a question I'm having.  I have a spreadsheet keeping track of every guest we're inviting.  For every name, there are five relevant columns here.
Column A - Bride's Mom/Stepfather's Guests
Column B - Bride's Dad/Stepmother's Guests
Column C - My mom's guests
Column D - Our friends/Wedding party
Column E - # of people who accept the invitation in that family

I want to keep track of each family's guests, so how many they're inviting and how many accept in each group.  I'm trying to write a formula that will sum up the values in Column E, but only if they have a corresponding number in Column A-D (so for her Mom's guests, sum up the number of guests who accept in column E but only if they have a value in column A).

Some of the solutions I've found will do it per line (check if a value exists then sum), but I'm looking to condense it all into one total number.  SUMIF seems to be the right way to go, but I'm not familiar with the syntax of it and I'm having trouble getting it right.

Please let me know if I can expand in any way.  Thank you!
----
https://docs.google.com/spreadsheet/ccc?key=0An4axc1NbXvEdHhYR2J6ZmZ4NE95bUw5dC1ZdTNuMGc

There's some sample data with the link.  Hopefully that should work :)
---------------------------------------------------------------------------------------
following is a solution to the problem