Daily/Weekly Count Formula Needed

Beronica Muller

Is there a formula to create daily/weekly views to appear in the dashboard? I need assistance with the following:

• Daily views for the last week of File Status
• Weekly view for the last four weeks of File Status
• Daily view for the last week of View Files
• Weekly view for the last four weeks of View Files
• Individual yellow Received Status
• Individual red Received Status
• Combined yellow and red Received Status
• Check to see if there is a way to eliminate those that are in a red Received Status that are marked complete.

The File Status column is color coded and I will need to create a daily/weekly view of based on the date that is in the Date File Expected column. Here is what I have so far and received an "INVALID OPERATION" error.

=COUNTIFS({Accrue Funding/Enrollment File Tracking Range 1}, "Green", {Accrue Funding/Enrollment File Tracking Range 1}, "Red", {Accrue Funding/Enrollment File Tracking Range 1}, "Yellow", AND(@cell >= DATE(2021, 5, 31), @cell <= DATE(2021, 6, 30)))

I know that this is a lot and a little confusing. Any assistance that you can provide is GREATLY appreciated. Here is a screenshot of the smartsheet with the column headings.

Jaykel Torres

Hey @Beronica Muller,

In reviewing the formula provided, it appears you are missing an argument and there may be some logical errors. Your formula is looking for the criteria of Green, Yellow, and Red to exist at the same time, which is impossible in your case. You will want to add multiple COUNTIFS Formulas to get the total of all Statuses. For example:

=COUNTIFS({range}, "Green") + COUNTIFS({range}, "Red") + COUNTIFS({range}, "Yellow")

Towards the end of your Formula, there is no Range referenced before the final Criteria:

=COUNTIFS({Accrue Funding/Enrollment File Tracking Range 1}, "Green", {Accrue Funding/Enrollment File Tracking Range 1}, "Red", {Accrue Funding/Enrollment File Tracking Range 1}, "Yellow", {range}, AND(@cell >= DATE(2021, 5, 31), @cell <= DATE(2021, 6, 30)))

I hope this helps!

Jaykel

Beronica Muller

@Jaykel T. Thank you. This has worked for me. I have another question. Would I use a similar criteria when trying to create a formula for daily and weekly counts from a particular range? For example, I needed a daily and weekly count from the Date Processed column from 05-01-2021 through 6-08-2021.

=COUNTIFS([Date Processed:Date Processed], AND(@cell >= DATE(2021, 5, 01), @cell <= DATE(2021, 6, 07)))

I am not sure if I have the correct range for the criteria.

Beronica Muller

@Jaykel T. you have the correct range. How can I tweak this similar formula to get a daily count for this same date range? For example, I would need to know how many blanks are in the Date Processed column for 05-01-2001, 05-02-2001, etc.

Jaykel Torres

Hey @Beronica Muller,

You may want to utilize the TODAY and WEEKNUMBER Functions to get a daily/weekly count of Date Values. I have created the example below on how this may look like:

Daily Count: =COUNTIFS(Date:Date, ISDATE(@cell), Date:Date, TODAY())

Weekly Count: =COUNTIFS(Date:Date, ISDATE(@cell), Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()))

If you're looking for Rows that do not have a Date Value within it, try using the NOT and ISDATE function together:

=COUNTIFS(Date:Date, NOT(ISDATE(@cell)))

I hope this helps!

Jaykel

Beronica Muller

@Jaykel T. Yes this works. One more question. I will need to exclude all items that have a check inthe File Cancel column. How would I add an IF statement so that they are excluded form the daily and weekly counts? Hope that I was clear in asking this question.

Thanks!

Jaykel Torres

Hey @Beronica Muller,

As the COUNTIFS Function can evaluate multiple criteria, you can define an additional range/criteria for your Checkbox Column:

Daily Count: =COUNTIFS(Date:Date, ISDATE(@cell), Date:Date, TODAY(), Checkbox:Checkbox, 0)

Weekly Count: =COUNTIFS(Date:Date, ISDATE(@cell), Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), Checkbox:Checkbox, 0)

It is important to note that when evaluating Checkbox Type Columns within a Formula, 1 represents a checked box while 0 is an unchecked box.

Jaykel