Weekly Tracking for a Dashboard
I have a grid where I am trying to lookup from a grid that people can log issues on. The first lookup is the date range, we want to count all issues within that date range. Then we only want to count by the "Field". On the log it is the column titled Field it is a dropdown of those fields listed on the Weekly trend grid as you can see our COUNTIFS formula is returning zero results. We tried to switch around with no luck. I can get it to populate with just the Filed, however when I try and add a DATE or WEEK, that is when I get "0". Thank you for any advice the community may have.
Comments
-
Paul Newcome ✭✭✭✭✭✭
You need to use an@cellreference.
=COUNTIFS({Range},@cell<= DATE, {Range},@cell>= DATE............)
Another thing to keep in mind is that you can use AND and OR functions with the criteria if you are referencing the same range more than once. In this case it would be something along the lines of...
=COUNTIFS({Range}, AND(@cell<= DATE,@cell>= DATE), .............)
-
Paul Newcome ✭✭✭✭✭✭
-
jhank ✭
Paul as I am looking at this formula, I have also tried to possibly simplify for future employees to just update the WEEKNUMBER, I keep returning an Invalid Data Type. Do you believe it can be written Simpler with the WeekNumber function?
=COUNTIFS({AP/Doc Image Collaboration Range 3},@cell>= DATE(2019, 2, 24), {AP/Doc Image Collaboration Range 3},@cell<= DATE(2019, 3, 1), {AP/Doc Image Collaboration Range 1}, [Primary Column]10)
I tried tor replace >=DATE() with =WEEKNUMBER(12) for example.
-
Paul Newcome ✭✭✭✭✭✭
The WEEKNUMBER function requires a date. I have built a few year to date tracking sheets and have found a few different ways to make things easier for other people to maintain in the future. The easiest is to use a cell reference within your formula so people can just update a cell and leave the formula alone.
To pull a specific week number, you would reference it this way...
WEEKNUMBER(@cell)= ##
If you would like help setting up specific cell references as I mentioned above, let me know. It's actually pretty simple and straightforward and saves A LOT of trouble when people don't need to access the formula itself.
-
jhank ✭
Paul any information you have on that would be useful. This is my biggest worry is that someone tries to update formulas and doesn't perform that function accurately. I was able to input the formulas however it looks like my -2 and -3 week data will not update. Current week and -1 is accurate so if there is possibly a better way I am open to that. Thank you
Current Week formula: =COUNTIFS({AP/Doc Image Collaboration Range 3}, ISDATE(@cell), {AP/Doc Image Collaboration Range 3}, WEEKNUMBER(@cell)= WEEKNUMBER(TODAY(+1)), {AP/Doc Image Collaboration Range 1}, [Primary Column]2)
-1 Week formula: =COUNTIFS({AP/Doc Image Collaboration Range 3}, ISDATE(@cell), {AP/Doc Image Collaboration Range 3}, WEEKNUMBER(@cell)= WEEKNUMBER(TODAY(-1)), {AP/Doc Image Collaboration Range 1}, [Primary Column]2)
-2 Week formula: =COUNTIFS({AP/Doc Image Collaboration Range 3}, ISDATE(@cell), {AP/Doc Image Collaboration Range 3}, WEEKNUMBER(@cell)= WEEKNUMBER(TODAY(-2)), {AP/Doc Image Collaboration Range 1}, [Primary Column]2)
-3 Week Formula: =COUNTIFS({AP/Doc Image Collaboration Range 3}, ISDATE(@cell), {AP/Doc Image Collaboration Range 3}, WEEKNUMBER(@cell)= WEEKNUMBER(TODAY(-3)), {AP/Doc Image Collaboration Range 1}, [Primary Column]2)
-
Paul Newcome ✭✭✭✭✭✭
The - # within the TODAY function is subtracting days from TODAY's date. To subtract an entire week number, you would use
WEEKNUMBER(TODAY()) - 1
The WEEKNUMBER function pulls an actual number. So for example, the week number for today is 14. If you want last week, then you want to display 13 which would be above. The week number before that (2 weeks ago) would be - 2, so on and so forth.
Since you are using the TODAY() function, your formulas will automatically update every time the sheet is opened. The only catch is when you get into having multiple years in the sheet. If that is an issue, a simple YEAR(TODAY()) could fix that.
For cell referencing that will enable anyone to change which information is displayed without having to edit the formula directly, you can use a cell in a Date type column. People would edit the date, and the formulas would reference that cell. So if the date people can edit is in the first row of [Date Column], you would replace TODAY() with $[Date Column]$1.
You can then lock the rows and/or columns that your data is displayed in, and people can easily view whatever date ranges they want. I used a similar setup where people can change a cell that simply contains the year.
I then have the simple instructions of saving the sheet as new and updating the year in that cell and they can now replicate whatever year to date data they want both accurately and consistently throughout the years.
-
jhank ✭
保罗,现在更多的意义。谢谢for taking the time to walk me through that. This information will help us make this automated (to an extent) to reduce the potential of a cell being broken. Thank you
-
Paul Newcome ✭✭✭✭✭✭
Happy to help. A lot of the people I build sheets for HATE complicated, and I can't blame them. So I have come up with more than a few tricks to accomplish a lot of automation and user friendliness.
Help Article Resources
Categories
Check out theFormula Handbook template!
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109490,"type":"question","name":"HAS exact match within multiselect - Numbered Values","excerpt":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…","snippet":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains)))…","categoryID":322,"dateInserted":"2023-08-25T19:26:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154049,"lastUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":16,"score":null,"hot":3386003780,"url":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","format":"Rich","lastPost":{"discussionID":109490,"commentID":392694,"name":"Re: HAS exact match within multiselect - Numbered Values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392694#Comment_392694","dateInserted":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/KJPRLKL2FW16\/capture-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:49:35+00:00","dateAnswered":"2023-08-25T23:58:23+00:00","acceptedAnswers":[{"commentID":392688,"body":"
Hi, <\/p>
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T14:46:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T14:46:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"