Counting cells with SUMPRODUCT and conditionals...?

[numerologist]

Hello, gurus –

I have a worksheet of resident data for an apartment building. The dat
is structured in such a way that each dweller has a record – which mean
that a family of four will show as 4 rows of data.

I want to simply find out how many adults and how many children ar
living in each apartment unit. I made three new columns on the attache
spreadsheet (F, G, H) to populate the tally.

I found a nice SUMPRODUCT formula that counts the number of matchin
communityid, leaseid, and residenthouseholdid... This was a relativel
simple way to identify multiple dwellers per unit. This is the formul
in column F:

=SUMPRODUCT(--(A2&B2&C2=$A$2:$A$50767&$B$2:$B$50767&$C$2:$C$50767))

This gives me the total number of people in each apartment. But I nee
to divide it into adults and children. I've tried to play around wit
the formula, but I can't get it to work. Children are indicated i
column E by both "Dependent" and "Minor Child" and I need to account fo
both in the formula. Here's what I had in column E, but it wasn'
returning the correct value:

=SUMPRODUCT(--(L22="Dependent")*(A22&B22&C22=$A$2:$A$50767&$B$2:$B$50767&$C$2:$C$50767)

The section of the worksheet in red easily highlights my goal. What I'
looking for are "4"s in all 4 family records for this apartment (whic
is already fine) AND all "2"s in all 4 records as well.

I can then perform a simple difference between columns F and H to fil
in the number of adults in each apartment.

Can anyone help me

+-------------------------------------------------------------------
|Filename: sandbox.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=616
+-------------------------------------------------------------------

 

[joeu2004]

I found a nice SUMPRODUCT formula that counts the number
of matching communityid, leaseid, and residenthouseholdid
[....] This is the formula in column F:
=SUMPRODUCT(--(A2&B2&C2=$A$2:$A$50767&$B$2:$B$50767&$C$2:$C$50767))

Although that might work, I think the following is a more intuitive and more
typical way to write that logic:

=SUMPRODUCT(--(A2=$A$2:$A$50767),--(B2=$B$2:$B$50767),--(C2=$C$2:$C$50767))

or equivalently:

=SUMPRODUCT((A2=$A$2:$A$50767)*(B2=$B$2:$B$50767)*(C2=$C$2:$C$50767))

We debate ad nauseum about which might be more efficient. Recent careful
measurements suggest that there is no statistical different. So it just a
matter of personal preference.

This gives me the total number of people in each apartment.
But I need to divide it into adults and children.
[....] Children are indicated in column E by both "Dependent"
and "Minor Child" and I need to account for both in the formula. [....]
I can then perform a simple difference between columns F and H
to fill in the number of adults in each apartment.

In H2:

=SUMPRODUCT((A2=$A$2:$A$50767)*(B2=$B$2:$B$50767)*(C2=$C$2:$C$50767)
*({"Dependent","Minor Child"}=$E$2:$E$50767))