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:

  1. a bar graph of the current month's task types | counts
  2. 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


Answers

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭

    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!

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

    @Brett Wyrick -

    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?

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭

    @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!

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭

    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!

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

    @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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!