"numerologist" wrote:
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$5076 7))

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.


"numerologist" wrote:
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))