Limit Results to Specific Year

Mike Jones

I have added the number of closed projects to a report by getting the total from the "Status" column in another sheet, like this.

=COUNTIF({ARC Range 2}, "Closed")

There is also a "Date" column in the sheet. How can I query that column also to limit the report results to just those from 2019?

Mike Jones

It worked!!!

Would you mind showing me how to do the same with a sum?

=SUM({ARC Range 3})

Paul Newcome

Try something like this (note the COUNTIFS with the S to allow multiple range/criteria sets)...

=COUNTIFS({ARC Range 2}, "Closed", {ARC Date Column}, IFERROR(YEAR(@cell), 0) = 2019)

Mike Jones

It worked!!!

Would you mind showing me how to do the same with a sum?

=SUM({ARC Range 3})

Paul Newcome

Happy to help! 👍️

You would use a SUMIFS. You would have the same range/criteria immediately after the range you would want to sum, so it would look something like this.

=SUMIFS({ARC Range 3}, {ARC Range 2}, "Closed", {ARC Date Column}, IFERROR(YEAR(@cell), 0) = 2019)

Please don't forget to mark the answer that most appropriately answered your original post so that others can know a solution has been found.

Mike Jones

The solutions provided for both my questions are perfect.

Thanks!!!

Paul Newcome

Happy to help! 👍️