Countif/Countifs Date Range

daviserl

I am trying to count a certain criterion that occurred within a specific date range and I can't seem to get the formula right.

=COUNTIF([Training Date]:[Training Date], AND(@cell >= DATE(2017, 6, 1), @cell < DATE(2018, 5, 31)), Role "Clinical")

I am trying to count, for example, how many clinical staff were trained between 6/1/17 and 5/31/18. I had blanks in my date range and ended up just filling those with a date outside of my criteria because the IFERROR wasn't correcting. Ultimately, I would like to be able to roll this up into another sheet and create a widget on my dashboard. Help?

Mike Wilday

Try something like this... The if error will ignore blank cells and compute those as a 0 count. So you don't have to worry about blanks. You'll have to modify this pull from an external sheet, but this will work in sheet and should give you a starting point. This countifs statement is checking for both requirements.

=COUNTIFS([Training Date]:[Training Date], IFERROR((@cell), 0) >= DATE(2017, 6, 1), [Training Date]:[Training Date], IFERROR((@cell), 0) <= DATE(2018, 5, 31))

 

Paul Newcome

Your format for the Role criteria is also incorrect. You would need to use something along the lines of Role:Role, "Clerical"

Mike Wilday

=COUNTIFS([Training Date]:[Training Date], IFERROR((@cell), 0) >= DATE(2017, 6, 1), [Training Date]:[Training Date], IFERROR((@cell), 0) <= DATE(2018, 5, 31), Role:Role, "Clerical")

That should include the role! Sorry, I forgot to put that in! 

@Paul, thanks. 

daviserl

Thank you for all of the help!

Paul Newcome

Haha. No need to thank me. I don't know why I didn't just go ahead and throw that in there. One of my clients has been a bear today, and its got my head all kinds of wonky.

Mike Wilday

You're welcome!