Rolling metric data sheet for chart that is current month and last 13 months
I'm trying to setup a metric data sheet that will automatically roll (update) without constant manual intervention. I don't believe I'm doing this right. We have multiple different types of task and I'd like a count by month of each that are In Progress.
I want to show this data in two charts on a dashboard:
- a bar graph of the current month's task types | counts
- historical line graph of the past 13 months (columns: 1 mth thru 13 mths) task types | counts
I am using a helper column (TODAY helper) instead of just TODAY(); I have a workflow automation rule that updates this daily.
I keep getting #INCORRECT ARGUMENT SET or #INVALID DATA TYPE. All my columns are text/number except the TODAY Helper - that is a date column.
Any help would be greatly appreciated.
Thanks Pegg
Best Answer
-
You need something more like this:
Current Month:
=COUNTIFS({Planned}, @cell = 1, {Completed}, @cell = 0, {Status}, @cell = "Green", {Task Type}, @cell = Task@row, {Due Date}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
Future Months:
=COUNTIFS({Planned}, @cell = 1, {Completed}, @cell = 0, {Status}, @cell = "Green", {Task Type}, @cell = Task@row, {Due Date}, IFERROR(MONTH(@cell), 0) = (MONTH(TODAY()) - 1 + IF(MONTH(TODAY()) - 1 < 1, 12, 0)))
In the above, both instances of "- 1" would change to however many months back you want to go. This is the case for -1 to -11 months.
For -12, you would use:
=COUNTIFS({Planned}, @cell = 1, {Completed}, @cell = 0, {Status}, @cell = "Green", {Task Type}, @cell = Task@row, {Due Date}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1))
Answers
-
Hello Pegg!
Quick question: what do you get if you use this formula:
=COUNTIFS(month({Due Date}),MONTH([Today Helper]@row))
Also, is {Due Date} a range?
If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick | Connect with me on LinkedIn.
------------------------------------------------------------------------------
2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!
-
Hello! Yes, Due Date is a range. I still get #INVALID DATA TYPE. Am I getting that because Due Date is a range and [TODAY Helper}@row isn't?
Maybe I've got my sheet setup wrong?
-
@Peggy Parchert - Yeah, you're spot-on. I was trying to figure out where the error is, and looks like it's there.
You'll want to utilize an INDEX/MATCH function pair to get a particular cell, but the range will need a unique identifier that's on this sheet AND the sheet you're wanting to get the data from. Is that possible?
If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick | Connect with me on LinkedIn.
------------------------------------------------------------------------------
2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!
-
Here's my post on INDEX and MATCH, explained in a detailed way with a step-by-step setup of two sheets. It's kinda lengthy, but it does explain it all.
INDEX and MATCH across two sheets - explained simply — Smartsheet Community
If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick | Connect with me on LinkedIn.
------------------------------------------------------------------------------
2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!
-
@Brett Wyrick - thank you. I'll have to review to see if I can use INDEX/MATCH for this. I'm not sure I can. I might have more questions. I'll keep you posted.
-
Hi Peggy, I'm wondering if this solved your problem? I'm interested in doing something similar and curious to see what your final formulas for current and past months looked like.
Thanks,
Kayla
-
You need something more like this:
Current Month:
=COUNTIFS({Planned}, @cell = 1, {Completed}, @cell = 0, {Status}, @cell = "Green", {Task Type}, @cell = Task@row, {Due Date}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
Future Months:
=COUNTIFS({Planned}, @cell = 1, {Completed}, @cell = 0, {Status}, @cell = "Green", {Task Type}, @cell = Task@row, {Due Date}, IFERROR(MONTH(@cell), 0) = (MONTH(TODAY()) - 1 + IF(MONTH(TODAY()) - 1 < 1, 12, 0)))
In the above, both instances of "- 1" would change to however many months back you want to go. This is the case for -1 to -11 months.
For -12, you would use:
=COUNTIFS({Planned}, @cell = 1, {Completed}, @cell = 0, {Status}, @cell = "Green", {Task Type}, @cell = Task@row, {Due Date}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1))
-
Thank you @Paul Newcome - I totally forgot about this.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 494 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
