How many employees make over $65K in the high school building?

Many times you are asked questions by your community or elected officials about staffing levels by building or classification. In previous posts, I’ve discussed how to use the Count function to determine how many employees had a title containing the words High School. Often you are asked more complicated questions that require multiple queries in a dataset. Here is an example. How many employees do we have working in the high school making over $65,000 with a Masters Degree? This question contains three distinct filters. We will use one formula to give the answer.

Countif Function

The Countif function allows you to count a column of data based on criteria. Then count the second column of data based on that criteria, and this continues. This allows you to write a quick formula when someone asks you a complicated question. The question had 3 parts.

  1. High School
  2. Salary over $65,000
  3. Master’s Degree

Use this Formula:

=countifs($E$2:$E$26,”*MA*”,$F$2:$F$26,”>$65,000",D2:D26,”*High School*”)

Learn how can automate this processes and you’ll never crack open a spreadsheet!