Saturday, September 22, 2012

yogi_Compute Number Of Children Of SubGroup Given The Number Of Children Of Each Member Of A Group

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #773  Sep 22, 2012

user cadmia said: (!category-topic/docs/spreadsheets/0BPLft_pCkU)
Comparing Two Ranges
Hello - 
I'm trying to pull together a summary comparing a smaller group to a larger group - For example:

Jane    1
Betty    2
Susan  3
Frank   4
Scott    5
Ernie    6

Then I have a summary page with a subgroup that only contains Jane, Betty and Scott, but I want to know how many children just those 3 have. I would expect the answer for that subgroup to be 8.

Right now I am doing a bunch of SUMIF statements (e.g. SUMIF = Betty + SUMIF = Jane + SUMIF = Scott), but I'm sure there is an array or something I could be using, or perhaps a SUMPRODUCT formula. Please help?

following is a solution to the problem