How to count number of employees within a department of that given month?

Question

So my question is this, lets say we have a table that has, Name, Department, Month, and I want to know how many people are in that Department of that given Month. I've tried a few different variations of COUNTIF, COUNTIFS, SUMPRODUCT(COUNTIF, etc. but can't quite get it. To show an example of it:

NAME   |  DEPARTMENT  |  MONTH
JOHN   |      A       |  FEBRUARY
DAVID  |      A       |  FEBRUARY
STEPH  |      B       |  FEBRUARY
MIKE   |      B       |  FEBRUARY
JOHN   |      B       |  MARCH
DAVID  |      A       |  MARCH
STEPH  |      A       |  MARCH
MIKE   |      A       |  MARCH

I would like the results to show as:

# Of People    DEPARTMENT   MONTH
   2         |    A      | FEBRUARY
   2         |    B      | FEBRUARY
   3         |    A      | MARCH
   1         |    B      | MARCH

How would I be able to do this within excel? I've tried multiple different formulas but so far no luck, so any help would be much appreciated!

Please let me know if anything needs to be further clarified.

EDIT: The formula I used is:

=COUNTIFS($C:$C,G$3,$B:$B,$F4)
G3 = February
F4 = Department 'A'

Which works to count all values, but this question came up after I posted this and tested it out. Let's say you have the same name in the same department and month, for example:

NAME  |  DEPARTMENT  |  MONTH
JOHN  |       A      |  FEBRUARY
JOHN  |       A      |  FEBRUARY
MIKE  |       B      |  FEBRUARY
MIKE  |       B      |  FEBRUARY

With the COUNTIFS statement above it would look like:

# OF People  |  DEPARTMENT  |  MONTH
     2       |      A       | FEBRUARY
     2       |      B       | FEBRUARY

Is it possible to put something in addition to the COUNTIFS to automatically combine duplicates? So instead of the answer up above it would show up as:

# OF People  |  DEPARTMENT  |  MONTH
     1       |       A      | FEBRUARY
     1       |       B      | FEBRUARY

This is the result I would want, counting the names as a unique value instead of duplicating the names.

Again please let me know if anything else needs to be clarified!

This should be just a COUNTIFS, so not sure why that wasn't working. Please share your formula and the error/wrong result you are getting? — Chrisvdberge
As has been said - COUNTIFS should work. Is the month just entered as text, or is it a real date formatted to show just the month (it says February in the cell, but the formula bar says something like 28/02/2018)? — Darren Bartrup-Cook
The COUNTIFS statement that I used is the following: =COUNTIFS($C:$C,G$3,$B:$B,$F6) G3 = February F6 = Department This does give the count, but what I'm wondering is, is there a way to get Unique values, for example if JOHN appears 5 times in department A in February, I want to count as only 1 for JOHN instead of 5 times. — Maykid

Curtis000 Curtis000 · Accepted Answer · 2018-10-18T13:31:32

=COUNTIFS([Range1],[Criteria1],[Range2],[Criteria2])

This is also a situation where you could use a Pivot Table, setting DEPARTMENT and MONTH as rows, and NAME as values with a count calculation.

How to count number of employees within a department of that given month?

1 Answers