I want to count items within current month without needing to change the month manually

Elizabeth Jones

oh goodness; thanks so much for the correction. works great!

Paul Newcome

@Elizabeth Jones Happy to help. 👍️

Stian Johansen

Hi @Paul Newcome ,

I can't get the formula to work with reference to another sheet. It works perfect in the same sheet, but #UNPARSEABLE when referencing.

=COUNTIFS({Another sheet}:{Another sheet}; AND(IFERROR(MONTH(@cell); 0) = MONTH(TODAY()); IFERROR(YEAR(@cell); 0) = YEAR(TODAY())))

Paul Newcome

@Stian Johansen When referencing another sheet, you only enter the range once like so...

=COUNTIFS({Another sheet}; AND(IFERROR(MONTH(@cell); 0) = MONTH(TODAY()); IFERROR(YEAR(@cell); 0) = YEAR(TODAY())))

Stian Johansen

Paul Newcome

Jan 27, 2021

@Stian Johansen When referencing another sheet, you only enter the range once like so...

=COUNTIFS({Another sheet}; AND(IFERROR(MONTH(@cell); 0) = MONTH(TODAY()); IFERROR(YEAR(@cell); 0) = YEAR(TODAY())))

That was quick and works! Can't think I didn't try that. Thank you :)

Paul Newcome

@Stian Johansen Happy to help. 👍️

Raul Cabrera

Paul Newcome

Mar 17, 2020

If putting the formula on a different sheet, you would only need to update the range (using the appropriate steps) to create a cross sheet reference.

=COUNTIFS([Order Date]:[Order Date], AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

=COUNTIFS({Source Sheet Order Date Column}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Excellent it works for me with some other arguments that i need to filter. Thanks Paul

Stephanie Jai

Paul Newcome

Jan 27, 2021

@Stian Johansen When referencing another sheet, you only enter the range once like so...

=COUNTIFS({Another sheet}; AND(IFERROR(MONTH(@cell); 0) = MONTH(TODAY()); IFERROR(YEAR(@cell); 0) = YEAR(TODAY())))

Paul Newcome

Mar 12, 2020

I will use a COUNTIFS to show an example of how the criteria section would look to reference the current month. The BOLD portion is going to be what specifies the current month and year without having to manually update.

=COUNTIFS([Range to Count]:[Range to Count], "specific text", [Date Column]:[Date Column], AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Paul - thank you some much, as many ways as I tried and searched I kept getting faulty answers using various formulas. The one from March really made the difference.