I want to count items within current month without needing to change the month manually
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?
Best Answer
-
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())))
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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())))
-
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.
-
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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.
-
@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.
-
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())))
-
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?
-
@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.
-
=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?
-
@Elizabeth Jones Make sure [Date Conducted] is a date type column and [counts] is a text/number type.
-
Confirmed both; really confused about the error.
-
@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))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!