# counting unique/distinct values (sample data attached)

1. ## 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

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

Originally Posted by justinhampton81
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. ## 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!

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

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

5. ## 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.

6. ## 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.

7. ## 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.

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

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

9. ## 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. ## 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).

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

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

Regards

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