+ Reply to Thread
Results 1 to 10 of 10

counting unique/distinct values (sample data attached)

  1. #1
    Registered User
    Join Date
    12-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    60

    counting unique/distinct values (sample data attached)

    I am developing a salary survey and need to count two fields: "Organizations" and "Incumbents". I can use the COUNTIFS function for the incumbents, but I have found the "Organizations" to be very difficult to count using a formula.

    Here is the practical example: 16 cities, with a revenue category of 100M to 200M reported 31 incumbents. This means in the spreadsheet there are 31 lines of data, and 16 have different values in the "City" field, though there are 31 different values in the "Incumbent" field. How would you go about calculating the number 16 to fill that cell (B5 in the example, below)?

    One point to note is that the dataset has roughly 120,000 rows of data and 250 different jobs, from 77 cities. If there was a COUNT_DISTINCT_IFS, that'd be perfect. Here's an example below and the data that was used to create this is attached.

    Assistant City Manager Orgs Incs 10th 25th 50th 60th 75th 90th
    Total Sample 52 110 $120,391 $130,776 $149,240 $159,862 $174,599 $198,804
    Revenue: Under 50,000,000 7 10 $60,559 $80,716 $123,905 $126,876 $129,646 $131,803
    Revenue: 50,000,000 - 100,000,000 15 23 $119,654 $128,431 $134,929 $138,259 $143,029 $152,435
    Revenue: 100,000,000 - 200,000,000 16 31 $120,490 $126,526 $140,780 $143,874 $156,025 $165,195
    Revenue: 200,000,000 - 500,000,000 7 17 $143,500 $152,838 $165,366 $170,000 $180,250 $183,737
    Revenue: 500,000,000 + 7 29 $166,354 $174,713 $180,069 $188,046 $202,837 $208,398
    Population: Under 50,000 17 22 $72,059 $120,246 $130,500 $133,108 $139,388 $149,531
    Population: 50,000 - 100,000 15 29 $120,312 $125,736 $134,930 $143,284 $145,227 $160,632
    Population: 100,000 - 200,000 10 23 $127,399 $137,992 $160,370 $165,504 $168,478 $180,250
    Population: 200,000 - 500,000 5 11 $139,826 $142,447 $152,838 $164,358 $181,785 $186,304
    Population: 500,000+ 5 25 $168,812 $174,713 $178,500 $195,000 $203,112 $213,278
    Median Income: Under 40,000 10 20 $105,074 $126,252 $131,498 $140,474 $143,874 $153,573
    Median Income: 40,000 - 50,000 11 31 $131,248 $140,832 $170,000 $174,034 $178,500 $195,390
    Median Income: 50,000 - 60,000 11 26 $116,698 $135,581 $169,239 $175,056 $195,546 $203,112
    Median Income: 60,000 - 70,000 9 15 $134,024 $144,451 $155,147 $159,277 $166,740 $176,532
    Median Income: 70,000 - 80,000 5 8 $118,046 $120,650 $128,499 $131,790 $141,080 $146,029
    Median Income: 80,0000+ 6 10 $118,965 $127,520 $138,210 $142,418 $163,714 $169,000
    Attached Files Attached Files
    Last edited by justinhampton81; 08-03-2014 at 06:56 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: counting unique/distinct values (sample data attached)

    Quote Originally Posted by justinhampton81 View Post
    Here is the practical example: 16 cities, with a revenue category of 100M to 200M reported 31 incumbents. This means in the spreadsheet there are 31 lines of data, and 16 have different values in the "City" field, though there are 31 different values in the "Incumbent" field. How would you go about calculating the number 16 to fill that cell (B5 in the example, below)?
    The practical example you noted above does not seem to correlate to any data in the sample workbook attached. If we're trying to look at a sample set of data and come up with the value 16 and 31 (B5 and C5) in your results table, we'd need a sample data set that would allow us to extrapolate that result.

    Can you update your sample work to:

    1) Include the results table given above
    2) Include raw data (a smaller subset) that would represent the data (and some extra to be ignored) we would use to calculate your B5 and C5 results?

  3. #3
    Registered User
    Join Date
    12-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: counting unique/distinct values (sample data attached)

    Hi J,

    Per your advice, I've updated the attachment. There are really 2 questions, highlighted in the spreadsheet. Thanks so much!
    Last edited by justinhampton81; 08-03-2014 at 06:57 PM.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: counting unique/distinct values (sample data attached)

    Sorry, the files looks the same. I see no pertinent data, nor highlighted questions.

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,514

    Re: counting unique/distinct values (sample data attached)

    I agree with JBeaucaire, there are no questions in your file.

    Maybe this pivot table will give you the information you wish.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  6. #6
    Registered User
    Join Date
    12-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: counting unique/distinct values (sample data attached)

    My apologies - I think I got the files mixed up. Here is a sample file that is directly linked to the data.
    Last edited by justinhampton81; 08-05-2014 at 12:01 AM.

  7. #7
    Registered User
    Join Date
    12-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: counting unique/distinct values (sample data attached)

    here's something probably more useful. It only has a couple of rows of data and is very simplified.

    any help is VERY much appreciated.
    Attached Files Attached Files
    Last edited by justinhampton81; 08-04-2014 at 10:51 PM. Reason: removed inline attachment

  8. #8
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,263

    Re: counting unique/distinct values (sample data attached)

    Hope it works.....pls see the file attach
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: counting unique/distinct values (sample data attached)

    Thank you so very much azumi. I think I'm good and then you guys just make me look like a novice. Thanks so much.

  10. #10
    Registered User
    Join Date
    12-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: counting unique/distinct values (sample data attached)

    Hi Azumi,

    I realized that I left out one variable and I'm unable to solve it based on your formula. If I need to only pull the numbers for the Assistant Fire Chief, how would I modify the code to exclude anything in Column B that isn't an assistant fire chief (like the police officers I added in the field). I've updated the spreadsheet. Thanks so much.

    (I did try to understand and modify the formula, but haven't had any luck).
    Attached Files Attached Files

  11. #11
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,263

    Re: counting unique/distinct values (sample data attached)

    I didnt' check the results, please have a look at the file attach...

    Regards
    Attached Files Attached Files

+ 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. Replies: 8
    Last Post: 01-23-2014, 05:28 AM
  2. Replies: 0
    Last Post: 09-12-2013, 06:24 AM
  3. Replies: 0
    Last Post: 03-08-2012, 12:18 PM
  4. Replies: 1
    Last Post: 03-02-2011, 04:14 PM
  5. Combining If and Lookup for Complicated Data (Sample is attached)
    By than_k22 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-15-2010, 01:13 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