Countif/Countifs Date Range

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?

Find more posts tagged with

Sheets

Healthcare

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Break Up Multi-Entry Row
I'm using a form entry (into an Intake Sheet) in which someone can select multiple locations for the same Initiative. I'm looking to copy the row to a different Master sheet, but would like to have the same row copied multiple times, once per location selected. Intake Sheet: Desired Result in Master Sheet: Basically, I…
Set contents of two columns into 1
Hi - trying to figure out a formula that combines two columns into one long column, maintaining contents in multiple rows. Noting that the number of items in each column will change as these are each using the index/distinct to summarize a separate sheet. For example: Thanks for any help!
Unparsable formula; Sheet summary SUMIF formula
Can anyone help why this formula is coming up as unparseable? I'm creating a sheet summary, where it totals the Estimated GP (Est. GP) column if the Status column has the "Quoted" drop-down option is selected. Formula i've used: =SUMIF(Status:Status,"Quoted",Est. GP:Est. GP) Thanks a lot!