# Need Help With COUNTIFS Formula That Includes Counting Only Unique Cells

1. ## Need Help With COUNTIFS Formula That Includes Counting Only Unique Cells

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.

2. ## Re: Need Help With COUNTIFS Formula That Includes Counting Only Unique Cells

Hi, welcome to the forum

I suggest you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

3. ## Re: Need Help With COUNTIFS Formula That Includes Counting Only Unique Cells

I need to know how many unique people live in County C, are Female and older than 20.
Try this...

Data Range
 A B C D E F G H I 1 First Name Last Name Full Name County Age Gender ------ County C 2 Jim Smith Jim Smith C 25 M Gender F 3 Jim Smith Jim Smith C 25 M Age 20 4 Jim Smith Jim Smith C 25 M 5 Sally Johnson Sally Johnson I 49 F Count 1 6 Sally Johnson Sally Johnson I 49 F 7 Rich Fox Rich Fox O 31 M 8 Sara Pool Sara Pool K 17 F 9 Sara Pool Sara Pool K 17 F 10 Judy Hilt Judy Hilt C 22 F 11 Judy Hilt Judy Hilt C 22 F 12 Judy Hilt Judy Hilt C 22 F 13

This array formula** entered in I5:

=SUM(IF(FREQUENCY(IF((D2:D12=I1)*(F2:F12=I2)*(E2:E12>I3),MATCH(C2:C12,C2:C12)),ROW(C2:C12)-ROW(C2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1