Date use with COUNTIFS formula (Non-Specific Date)

Matt Travis

I have set up a summary grid pulling information from another Support calls grid so I can keep track of logged calls completed by members of my team. I am currently using the formula below to pull that information for one of the team and it works fine.

=COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred")

The query I have is because I want to improve this. I would like to apply this to record only those calls completed in the current month and a second data set for only the current year. What would be the best way to accomplish this? I've only seen things for specific date ranges used so far which wouldn't be suitable for me.

Paul Newcome

My apologies. I clicked on "Submit" before posting the year...

=COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Date Range}, IFERROR(YEAR(@cell), 0) = 2021)

Paul Newcome

If you want current month and year, then you can use...

=COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Date Range}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Paul Newcome

For a single month (I will use March 2021 for this example) you wuld want:

=COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Date Range}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2021))

Paul Newcome

My apologies. I clicked on "Submit" before posting the year...

=COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Date Range}, IFERROR(YEAR(@cell), 0) = 2021)

Matt Travis

Thanks @Paul Newcome but if i'm reading this right, that references values as the current month or year. Is that right? So every time the month changed, I would have to go back in and change the number in the formula and same for the year.

There must be a way to make this an automated process. I have created a column that will host a cell showing today's year and another showing today's month. I would like to reference those.

I seem to have sorted this now via the formula below

=COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Support calls Range 4}, IFERROR(MONTH(@cell), 0) = [This Month]1)

The [This Month}1 refers to a column with my month date in as =MONTH(TODAY(0))

Paul Newcome

If you want current month and year, then you can use...

=COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Date Range}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Matt Travis

So much easier. Thank you

Paul Newcome

Happy to help. 👍️