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

TeeM · 03/12/20

I need to report on the number of items that appear in the current month to date.

Is there a formula that can be applied without necessitating manual entry of changing the month manually?

Paul Newcome · 03/17/20

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())))

Andrée Starå · 03/12/20

Hi Terry,

Yes, there is.

Where do you want to show the information? In the same sheet, Sheet Summary, Dashboard or somewhere else?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome · 03/12/20

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())))

TeeM · 03/13/20

Hello Andree - I actually don't mind as long as I can get it to work.

Hello Paul - Thanks for this. I will need to play around with this to get it to work for me. I appreciate the help.

Paul Newcome · 03/13/20

@TeeM Happy to help! 👍️

You should not have to edit the bold portion at all.

Andrée Starå · 03/15/20

@TeeM

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

TeeM · 03/16/20

@Paul Newcome

Hello Paul, I am still a bit stuck on your formula. I have selected the range to count which would be [ORDER DATE]

I am not sure what the "specific text" would be or the [Date Column] as I thought that would be the [ORDER DATE] column

The 'Created Date' column is not relevant in this instance.

Paul Newcome · 03/16/20

That is correct. In my solution above, you would need to edit the non-bold portions to fit your needs. If you don't need to specify a text string, then you can remove that part. If your date column has a different name, then you would need to update that section to reflect the correct column name.

The bold portion is the part that was the main focus on how to specify current month and year without having to manually update every month.

If all you want is to count order dates, then you would modify the formula to look something like this...

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

Which leaves the criteria portion intact and modifies the non-bold portion to fit your needs.

TeeM · 03/17/20

@Paul Newcome Hello Paul, that has worked on the source sheet as I wanted. Thank you.

If in the scenario where i wanted to have this formula reporting from a separate sheet i.e. the formula is on a separate report/sheet to the source data, what would I change on your formula in order for it to work? I tried just referencing the range as per your formula and I couldn't get it to work. On the Source Sheet Summary, it was fine.

Paul Newcome · 03/17/20

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())))

Elizabeth Jones · 09/09/20

I'm doing something similar but want to count dates per month. Example x orders in May, x orders in June, etc. How do I modify this to count the number per month?

Paul Newcome · 09/09/20

@Elizabeth Jones You would use the same formula with only some minor changes.

Your criteria would instead be

..............IFERROR(MONTH(@cell), 0) = ##........................

You basically replace MONTH(TODAY()) and YEAR(TODAY()) with the month and year numbers that you want to count for.

Elizabeth Jones · 09/09/20

Screen Shot 2020-09-09 at 1.46.12 PM.png

=COUNTIFS([Date Conducted]:[Date Conducted], AND(IFERROR(MONTH(@cell), 0) = MONTH(7), IFERROR(YEAR(@cell), 0) = YEAR(2019)))

@Paul Newcome I'm getting #invalid data type?

Paul Newcome · 09/09/20

@Elizabeth Jones Make sure [Date Conducted] is a date type column and [counts] is a text/number type.

Elizabeth Jones · 09/09/20

Confirmed both; really confused about the error.

Screen Shot 2020-09-09 at 2.09.58 PM.png

Screen Shot 2020-09-09 at 2.10.14 PM.png

Paul Newcome · 09/09/20

@Elizabeth Jones Sorry. I should have taken a closer look at your formula.

=COUNTIFS([Date Conducted]:[Date Conducted], AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2019))

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

Best Answer

Answers

Help Article Resources

Categories