Weekly Tracking for a Dashboard

jhank

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. 

Paul Newcome

You need to use an @cell reference.

 

=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), .............)

jhank

Thank you, Paul the formula worked.

 

=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]2)

 

 

Paul Newcome

Happy to help! 

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

Paul, that makes a lot more sense now. Thank you 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.