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?