Formula for requests due in a certain time frame.

Kaitlyn Carroll

Hi,

I'm trying to create a COUNTIFS formula for my metrics sheet.

=COUNTIFS({Creative Services Tracker Status}, <>"05_COMPLETE", {Creative Services Tracker Status}, <>"06_DEAD", {Creative Services - Requested Completion}, <=TODAY(7))

I'm trying to see a count of requests that are due within the next 7 days that DO NOT have a status of "05_COMPLETE" or "06_DEAD". The number that counts based on this formula (17), does not match with the number of requests listed on the report I built (10).

My report has these conditions:

Status is not one of "05_COMPLETE", "06_DEAD"

and

Requested Due Date is in the next 7 days

Any thoughts?

Thanks,

Kaitlyn

Kelly Moore

Hey @Kaitlyn Carroll

Is the report capturing any blanks?

Kaitlyn Carroll

Hi @Kelly Moore,

I didn't think about that. I just checked - I didn't have it filtered to exclude blanks but I just added that because I don't want it to capture blanks. Thanks for asking that clarifying questions.

Kelly Moore

Hey Kaitlyn

Did you ever figure out the source of the difference between the report and the formula?

I wondered if this worked

=COUNTIFS({Creative Services Tracker Status}, OR(@cell<>"05_COMPLETE", @cell<>"06_DEAD"), {Creative Services - Requested Completion}, <=TODAY(7))

Kaitlyn Carroll

Unfortunately, no. I never figured it out. Everything appears to line up, but the numbers are different.

Kelly Moore

Is it possible to show a screenshot of both sheet and also the criteria for your report?

Kaitlyn Carroll

Absolutely - great idea.

Here is the formula on the metrics report:

This formula is equaling to 9.

Here is screenshots of the report criteria:

And the report populates 12 requests right now.

Kelly Moore

Hey Kaitlyn

Your formula says that the criteria is less than TODAY(), not TODAY(7) as indicated in the formula I suggested. The 7 inside the brackets indicates the date is 7 days from today.

Please advise if the addition of TODAY(7) remedies the difference between report and calculation.

Kelly

Kaitlyn Carroll

When I add TODAY(7), the count is then 21. And the report is still at 12.

Kelly Moore

Kaitlyn

When I look at your report the responses are not identical to the responses in your sheet. Your report shows all caps, this is not what is in the sheet. Has the column changed over time and the report is still looking at an older version of the column?

Scroll down to the very bottom of the column section of the report and make sure the column name does not have a red mark by it

Also, does your DATE column have dates in all the cells. What happens if you add another condition to the report where the Date is a date?

Kelly

Kaitlyn Carroll

Hi @Kelly Moore,

Very good catch on the all caps. I did not see any red marks, but I decided to create a new report just to cover all the bases. The new report pulled the same number as the old one I was working with. I'm not sure how to create a condition that the DATE column must equal a date (but I did check that all cells under the date column are populated with a date.

Do you think it's the formula?

Kelly Moore

Hey

In case you need this in the future, here is how to add an IS DATE condition to a report. It only works on date columns.

I should have thought of this earlier - whenever troubleshooting a formula one can remove terms in the formula, one by one, to see if a single term is causing the error. I suggest we do this between the report and the formula to see if we can get the data to match. This is only temporary for trouble shooting purposes.

I suggest to first remove the date portion from both the report and formula and see if the numbers match. If they do not, remove the next term. If that matches, we'd put the date back. We keep messing with formula and report to see if we can isolate the problem.

Kelly Moore

Hey Kaitlyn

Is it possible to get a screenshot of your {Creative Services Tracker} sheet. One more question that I should have asked in the beginning. Is that Status column a multi-select dropdown - or a single select?