Rolling metric data sheet for chart that is current month and last 13 months

Peggy Parchert

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

Paul Newcome

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

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?

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

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

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

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.

kss5229

https://community.smartsheet.com/discussion/comment/310377#Comment_310377

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

Paul Newcome

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

Peggy Parchert

Thank you @Paul Newcome - I totally forgot about this.