CountIFs between two dates

Genevieve P.

Hi @Angie F

Grouped Reports can be used for a chart! 🙂

As long as they have only one Grouping and also have Summary enabled, you can use this as a source for a chart.

I like the idea of a Record a Date but it will always record today's date, when the automation runs. Then yes, you can use a formula to get the Monday and Tuesday dates.

An alternative would be to use a formula to return a weeknumber - adjusted for your specific week (starting on Tuesday) and then your formulas will be way simpler, as we can look for a number instead of a date.

How are you determining when a task is assigned within a specific week?

Angie F

@Genevieve P. I haven't set up any week numbers in the task list. The record a date is working okay for my helper date and Tuesday date columns but for Monday it wants to record the wrong Monday. I need it to record the following Monday and not the Monday the automation runs on. What if I put a formula in my Monday column to add 6 days from the date in the Tuesday column?

Angie F

@Genevieve P. I finally got it to work right! Now I just have one more I need help with at the moment. Any suggestions on how I could do this one? The others could be anyone in our organization besides Chad and Andy. Is there a way to say does not equal Chad or Andy? Or am I going to have to create a formula for each person and then SUM those into the number of tasks assigned to others?

Angie F

@Genevieve P. and this formula is counting wrong. Andy has only completed 4 tasks between the dates but it's counting 5. Here is the formula:

Here is where it's pulling from:

Genevieve P.

https://community.smartsheet.com/discussion/comment/415862#Comment_415862

Hey @Angie F

For this question, you can use <> to say "does not equal" and then use this in your COUNTIFS:

=COUNTIFS({Who}, @cell <> "Andy", {Who}, @cell <> "Chad", ... and so on.

Genevieve P.

https://community.smartsheet.com/discussion/comment/415869#Comment_415869

For this question, can you try adding a filter on to the sheet with your criteria to see what comes up? Keep in mind the date criteria says "greater than or equal to" and "less than or equal to" so you're including both Monday and Tuesday in the count.