Tag Archives: Range

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.

Just for Fun… Roll the Dice

Picture this…

You’re settling down for a game of monopoly… everyone is ready around the board… you’ve exchanged banter and already started to forge alliances… and you realise you don’t have any dice! Somewhere along the last 20 years that you owned the board, the dice got separated from it, eaten by the dog maybe, who knows.

So what now?

Use Excel.

[showmyads]

Here it is, your dice-rolling spreadsheet. Just select from the dropdown how many dice you want to roll (up to 6 at a time) and press the button (make sure you have macros enabled first).

Roll the Dice
Roll the Dice

If you’re learning VBA, this is a relatively short piece of code for you to investigate. See if you can understand how it works, and think whether you would do it the same way. If not, let me know what your approach would be!

 
[showmyads]

UPDATE!:

We took some good advice and changed our VBA to avoid selecting cells. It simplifies things and with a larger file is more efficient – basically it’s the better approach. Also, if you turn your volume on that it now reads you the numbers as they come up and tells you the total at the end. And another thing, it has a Roll again? dialogue box at the end.

Excel Keyboard Shortcuts – Function Keys

More Excel keyboard shortcuts – the Function keys

F1 – Excel Help

F2 – Edit active cell

F3 – Brings up the Paste Name box – allowing you to easily refer to any named ranges

F4 – Repeats the last action (or in a formula cycles through absolute reference options)

F5 – Go To

F6 – Flicks through the worksheet, ribbon, task pane, and zoom controls

F7 – Spell checker

F8 – Toggle Extended Selection – keeps highlighting cells until you switch it off

F9 – Calculate now (on active worksheet)

F10 – Just like pressing Alt

F11 – Create chart – uses range around the active cell

F12 – Save As