Formula for highest education level
I am currently creating a visual for 150+ people's education levels, dropdown columns shows a majority of people has up to an masters degree but around 10 of them have doctorate/Ph.D. on top of it. What is a formula I can use to count the number of master degree holding individuals whilst EXCLUDING those with doctorate/Ph.D. so I could get a reading on how many people's highest education level is a masters degree?
Best Answer
-
Try this:
=COUNTIFS(Education:Education, AND(HAS(@cell, "Masters"), NOT(HAS(@cell, "Doctorate"))))
Answers
-
=COUNTIF([education level]:[education level], <>"doctorate/Ph.D.")
...
-
Hi Heyjay, this is great! However because most that have a master's degree also has bachelors, it is being included in the final number as well, how would I write it so it factors out Doctorates AND Bachelors?
-
Are you able to provide a screenshot for context?
-
Yes….so basically I need a function that counts of the amount of cells that contains the highest education level up to only masters, excluding those have achieved a Doctorate or stayed as Bachelors.
-
Try this:
=COUNTIFS(Education:Education, AND(HAS(@cell, "Masters"), NOT(HAS(@cell, "Doctorate"))))
-
Thank you so much Paul!
-
Happy to help. 👍️
-
Out of curiosity and assuming you'll never have someone listed as a Doctorate without both Masters and Bachelor or a Masters without a Bachelor as well…
Does this give the same counts?
Bachelor:
=COUNTIFS(Education:Education, COUNTM(@cell) = 1)Masters:
=COUNTIFS(Education:Education, COUNTM(@cell) = 2)
Doctorate:
=COUNTIFS(Education:Education, COUNTM(@cell) = 3)
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives