Tag Archives: headcount

XL FORMULA FOCUS: COUNTIF to Calculate Headcount

 

No HR Dashboard is complete without a headcount summary, and Excel has given us the COUNTIF function to make it easy!

[showmyads]

It’s a pretty straightforward formula – this is how Excel describes it: “Counts the number of cells within a range that meet the given criteria”. So all you need is a range of cells and the value that you are looking for within that range. For example if you had a list of staff and their department, you could count how many are in your Finance team.

Here’s what it looks like:

COUNTIF ( range , criteria )

So if your table has a column with departments listed, that’s your range – let’s say it’s C2:C14. If you want to count how many of those say Finance, you can put “finance” as your criteria (don’t worry, it’s not case sensitive”). Your formula would look like this:

COUNTIF ( C2:C14, “finance” )

If you’re using this formula for a headcount summary on a dashboard, you’ll want to show more than one department, in a table something like this:

If that’s the case you won’t want to keep changing the criteria part of the formula in each cell, like this:

COUNTIF ( C2:C14, “finance” )

COUNTIF ( C2:C14, “HR” )

COUNTIF ( C2:C14, “IT” )

COUNTIF ( C2:C14, “Marketing” )

COUNTIF ( C2:C14, “Sales” )

[showmyads]

Instead, you can just reference the cell to the left, in the Department column, and make the range part of the formula a static range by adding $ signs (you can just press F4 as a shortcut) like this:

COUNTIF ( $C$2:$C$14, E2)

Note that the we haven’t made the criteria reference static as we want this to follow down column by column to change the department.

Here’s what it would look like:

Of course on this small table you can quickly count things yourself, but when you have a table with dozens or even hundreds of rows the COUNTIF function can save you an awful lot of time.