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

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?

Hi J,

Per your advice, I've updated the attachment. There are really 2 questions, highlighted in the spreadsheet. Thanks so much!

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

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

Maybe this pivot table will give you the information you wish.

My apologies - I think I got the files mixed up. Here is a sample file that is directly linked to the data.

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.