Formula on Template

tmichelle068

Hi all! I am really stumped by a formula on a template I am using, and need some help.

The Inspection Tracking Template has a form, metrics, then dashboard. I am trying to edit the metrics to show the columns I added on the form. There is a section where it takes the "Date" column and calculates violations per month. The formula in there now is: =COUNTIFS({MED1}, "No", {Date}, MONTH(@cell) = $[Month#]@row)

When I paste that to my new information, it obviously doesn't work, so I have 2 questions.

1. What does this part of the formula mean? {Date}, MONTH(@cell) = $[Month#]@row) I need it to take the date entered and summarize it into just the Month.
2. How do I make the initial range of MED1 cover multiple columns? I tried MED1:MED5, but that didn't work.

Any help is appreciated! Thanks!

Ryan Sides

Hey @tmichelle068 , the {Date} is called a cross sheet reference. You build (and see) these by right clicking on your sheet and selecting "Manage References"

1) So this is saying, go look at the entire column I selected in my cross sheet reference {Date} and if the Month of each row's Date is equal to the Month on the row of this formula, then include it in the COUNT.

2) You would go build another cross sheet reference (by right clicking, etc.), give that new reference a name (like MED2) and use it in your formula as {MED2}.

The MED1:MED5 is telling Smartsheet to look for a column on your current sheet called MED and use ROW 1 through 5.

Ryan Sides

Hey @tmichelle068 , the {Date} is called a cross sheet reference. You build (and see) these by right clicking on your sheet and selecting "Manage References"

1) So this is saying, go look at the entire column I selected in my cross sheet reference {Date} and if the Month of each row's Date is equal to the Month on the row of this formula, then include it in the COUNT.

2) You would go build another cross sheet reference (by right clicking, etc.), give that new reference a name (like MED2) and use it in your formula as {MED2}.

The MED1:MED5 is telling Smartsheet to look for a column on your current sheet called MED and use ROW 1 through 5.

tmichelle068

Thank you! I got the formula to work, but need to know if I understand it correctly, and have a follow-up question now.

1. So, =COUNTIFS({MED1}, "No", {Date}, MONTH(@cell) = $[Month#]@row) says If the answer in MED1 is No, and the date entered matches the date of this row, count it. Right?
2. How do I make the formula repeat to calculate the same information on MED2, MED3, AND MED4? I tried repeating the formula and selecting the new column, but that wouldn't work.

Ryan Sides

@tmichelle068

1). No it says if the MONTH matches, count it. Not the entire date.

2) You would go build another cross sheet reference (by right clicking, etc.), give that new reference a name (like MED2) and use it in your formula as {MED2}. Like

=COUNTIFS({MED1}, "No", {Date}, MONTH(@cell) = $[Month#]@row) + COUNTIFS({MED2}, "No", {Date}, MONTH(@cell) = $[Month#]@row)

That formula adds the count of MED1 and the count of MED2 together. Is that what you're looking for?

tmichelle068

Yes!!! Thank you so much for your help!!!

Ryan Sides

You bet!

Heather_Verde

So this {Date}, MONTH(@cell) = $[Month#]@row) is saying look at the range reference named "Date" from the sheet: Sheet - Inspection Submissions with Form and specifically compare the Month at the cell level in that range to the absolute (never changing) Month# row on the sheet: Sheet - Inspection Metrics.

As for the multiple column part of the formula, I tried adding the multiple columns to the range, but got an error. So what you may need is a helper column that counts all of the "No" responses per row on the Sheet - Inspection Submissions with Form sheet.

Example:

Column name - "No Count"

Formula - =COUNTIFS([Medical - First Aid Kit]1:[Medical - Emergency Numbers]1, "No")) **Note: this formula currently only includes the 3 columns shown on that sheet, but of course you would add the range to include any new columns you added)

Then on the Sheet - Inspection Metrics sheet have a column to capture the sum of all of the "No" responses for the Month of the row

Example:

Column name - "All Med Violations"

Formula - =SUMIFS({NoCount}, {Date}, MONTH(@cell) = $[Month#]@row) **Note that the {NoCount} and {Date} references are range names for the appropriate columns on the Inspection Submission sheet.