Hi
I have spent many hours trying to solve this one on my own and just cannot find the piece I am missing. Here is my problem: I have a spreadsheet I am formulating to pull in different data from a master spreadsheet. One of the formulas I need to come up with would count the number of row entries on the spreadsheet that meet multiple criteria, i.e. age, gender, county of residence and only unique names (there are many entries for each name on the master spreadsheet). Here is a brief example of the master spreadsheet I am referencing, keeping in mind the master spreadsheet as over 3000 row entries:
Col A Col B Col C Col D Col E Col F Col G
First Name Last Name Full Name County Age Gender
Jim Smith Jim Smith C 25 M
Jim Smith Jim Smith C 25 M
Jim Smith Jim Smith C 25 M
Sally Johnson Sally Johnson I 49 F
Sally Johnson Sally Johnson I 49 F
Rich Fox Rich Fox O 31 M
Sara Pool Sara Pool K 17 F
Sara Pool Sara Pool K 17 F
Judy Hilt Judy Hilt C 22 F
Judy Hilt Judy Hilt C 22 F
Judy Hilt Judy Hilt C 22 F
I need a formula that will give the total number of unique name entries that meet certain county, age and gender criteria. I created column C to concatenate Columns A and B to make the formula less complex. I tried using COUNTIFS and was successful up until the point where I needed to only count unique name entries. As an example, I need to know how many unique people live in County C, are Female and older than 20. I can only get this far on the formula... =COUNTIFS(F2:F3000, "F",E2:E3000,">20",D2:D3000,"C") but I cannot find a way to include the # of unique names (Col C) in this COUNTIFS formula. This is driving me crazy. How do I make this work? I appreciate any help that is out there.
Bookmarks