Formula on Template
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.
- 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.
- 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!
Best Answer
-
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.
Answers
-
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.
-
Thank you! I got the formula to work, but need to know if I understand it correctly, and have a follow-up question now.
- 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?
- 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.
-
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?
-
Yes!!! Thank you so much for your help!!!
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 296 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!