Formula for highest education level

Gieen

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?

Paul Newcome

Try this:

=COUNTIFS(Education:Education, AND(HAS(@cell, "Masters"), NOT(HAS(@cell, "Doctorate"))))

heyjay

=COUNTIF([education level]:[education level], <>"doctorate/Ph.D.")

Gieen

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?

Paul Newcome

Are you able to provide a screenshot for context?

Gieen

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.

Paul Newcome

Try this:

=COUNTIFS(Education:Education, AND(HAS(@cell, "Masters"), NOT(HAS(@cell, "Doctorate"))))

Gieen

Thank you so much Paul!

Paul Newcome

Happy to help. 👍️

Paul Newcome

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)