+ Reply to Thread
Results 1 to 3 of 3

Need Help With COUNTIFS Formula That Includes Counting Only Unique Cells

  1. #1
    Registered User
    Join Date
    03-19-2015
    Location
    Minneapolis, MN
    MS-Off Ver
    2013
    Posts
    1

    Unhappy 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. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,434

    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.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. COUNTIFS Formula with Unique Values
    By scuppasteve in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-17-2015, 06:22 AM
  2. COUNTIFS not counting all cells
    By Doppelganger in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-05-2015, 04:53 PM
  3. If then formula that includes counting
    By nclaus1018 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-20-2013, 03:21 PM
  4. Countifs includes dates in different format
    By mosheva in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-11-2013, 02:48 AM
  5. [SOLVED] Counting Cells that includes exact match and also if a cell includes specific text
    By smclachlan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-19-2012, 07:42 PM

Bookmarks

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