This tutorial was prompted by a question on one of the LinkedIn groups I’m a member of. An IT Technician has a spreadsheet that displays a list of tickets submitted to the HelpDesk. Each ticket has a status (for example Resolved, Pending, etc) and a date. The Technician needs to know how many tickets have a status of Resolved AND a date of today.
In the screenshot below, it is assumed that today is 27th February 2013. The dates are formatted as mm/dd/yyyy h:mm.
The function to calculate the number of tickets “that have a status of Resolved AND a date of today” is in F3. The function is:
The structure of the COUNTIFS function is
COUNTIFS(Range1, Criteria1, Range2, Criteria2, Range3, Criteria3,…)
The COUNTIFS function does work with dates but in this case returns a value of 0 because of the time element. The way I have solved this is to put a function in column C to extract the “serial number” of each date in column A.
If you don’t understand what a date serial number is, here’s a brief explanation…
When you type a date into a cell, what is actually stored in the cell is a number (known as a serial number). This number represents the number of days since 1st January 1900 (Excel’s “base date” – Excel doesn’t recognise dates before this date).
When you type a time into a cell, what is actually stored in the cell is a decimal number which represents a fraction of a day, so for example, 12:00pm is 0.5 (50% of the day), 6:00 in the evening is 0.75 (75% of the day).
So, although cell A12 in the screenshot above displays as 02/27/2013 8:00, the underlying value in the cell is 41332.33333 (to see this, format the cell as General or Number).
Back to our Technician’s problem. We aren’t interested in the time element, just the day part of the serial number. In column C, I’ve used the INT function which extracts just the Integer element of a number, so for example, C2 contains the function =INT(A2).
So to review, the function to calculate the number of tickets “that have a status of Resolved AND a date of today” is in F3. The function is: =COUNTIFS(B2:B12,”Resolved”,C2:C12,41332).
In other words, count the number of times the word “Resolved” appears in the range B2:B12 AND 41332 is the value in C2:C12
Download the sample spreadsheet here