If, sumproduct or other?

[Dan]

I am trying to come up with a formula that counts the
number of "Single-Males" living in a dorm by theirself.
The spreadsheet looks like the following:
A B C D E F G H
1 Rm# Name Sex Age Marital Employer Pos. Salary
2 Status
3 101 Adam M 20 S STUDENT 11 0
4 102 Bob M 24 S NASA 01 34500
5 Curt M 28 S NASA 01 36900
6 103 Deb F 22 M ACME SUP 03 18250
7 Earl M 21 M DOMINOS 03 9890
8 104
9 105 Fran F 19 S STUDENT 11 0
10 106 Gary M 20 S 10 0

There should only be 2 that are counted, #101 & #106.
While there are 2 other single males in the population
(#102), they dwell in the same dormroom and I don't want
them counted. Any idea's? This is getting real
frustrating.

 

[Frank Kabel]

Hi Dan
is it possible for you to add an additional column whcih stores if one
lives in a single dorm or not?

 

[Bob Phillips]

You need to replicate the room number in column A and then you can use

=SUMPRODUCT((C2:C9="M")*(E2:E9="S")*(COUNTIF(A2:A9,A2:A9)=1))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)