Counting cells with SUMPRODUCT and conditionals...?
 

Hello, gurus –

I have a worksheet of resident data for an apartment building. The data is structured in such a way that each dweller has a record – which means 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 are living in each apartment unit. I made three new columns on the attached spreadsheet (F, G, H) to populate the tally.

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

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

This gives me the total number of people in each apartment. But I need to divide it into adults and children. I've tried to play around with the formula, but I can't get it to work. Children are indicated in column E by both "Dependent" and "Minor Child" and I need to account for both in the formula. Here's what I had in column E, but it wasn't 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'm looking for are "4"s in all 4 family records for this apartment (which 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 fill in the number of adults in each apartment.

Can anyone help me?

Counting cells with SUMPRODUCT and conditionals...?
 

"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))