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
Bookmarks