Stuck on completed in the last 7 days formula / function

Molly12345

Hi I keep getting incorrect augmentation for this cross sheet formula =COUNTIF({Sheet 1 Execution completed}, =TODAY(-7), +COUNTIF({sheet 2 execution completed date}, =TODAY(-7))).

When I out <= Today(-7) I got numbers that did not exist.

I am new to smartsheet and trying to learn via youtube and here.

Many thanks in advance :)

Jeff Reisman

@Molly12345 You're almost there. You need to do these counts as separate formulas, which are then added together. So the first COUNTIF needs to end before the plus sign.

=COUNTIF({Sheet 1 Execution completed}, =TODAY(-7)) + COUNTIF({sheet 2 execution completed date}, =TODAY(-7))

David Tutwiler

I'm glad you've got it going!

David Tutwiler

This could be very simple. You do not need the (=) in your formula as it is assumed as part of the COUNTIFS statement. Try:

=COUNTIF({Sheet 1 Execution completed}, TODAY(-7)) + COUNTIF({sheet 2 execution completed date}, TODAY(-7))

Jeff Reisman

@Molly12345 You're almost there. You need to do these counts as separate formulas, which are then added together. So the first COUNTIF needs to end before the plus sign.

=COUNTIF({Sheet 1 Execution completed}, =TODAY(-7)) + COUNTIF({sheet 2 execution completed date}, =TODAY(-7))

Molly12345

Hi guys thank you so much for your help!! This formula is not coming up unsurpassable anymore, however it won't count the dates. I keep getting 0 despite there being a date in the last 7 days. Any suggestions on what I am doing wrong? Thank you in advance 😊

David Tutwiler

I think there might be a misunderstanding with the formula. The way it stands now, it is looking for dates that are exactly TODAY - 7 days. If we're looking for a range we'll likely need to use COUNTIFS like this:

=COUNTIFS({Sheet 1 Execution completed}, @cell > TODAY(-8), {Sheet 1 Execution completed}, @cell < TODAY(+1)) + COUNTIFS({Sheet 2 Execution completed}, @cell > TODAY(-8), {Sheet 2 Execution completed}, @cell < TODAY(+1))

This creates the range of the last 7 days by verifying that the date is greater than 8 days ago and less than 1 day from now (tomorrow).

Molly12345

Thank you so much for your insight ! I am new to SS so this is a great help.Will try this and report back 😀

David Tutwiler

No problem, I hope it helps.

Molly12345

Hi,

unfortunately it is still not working. I am checking an entire column as opposed to a cell. I just want to count what is completed in the last 7 days inc today :)

I will then take this formula and edit for what is due in the next 7 days inc today

Many thanks in advance 😀

David Tutwiler

That formula should be working, so I would check to make sure all of the other parameters are set correctly. Can you confirm that the column you are pointing to with the cross sheet reference is set to the type Date and not to Text/Number? Below is an example on my test sheet with the exact same formula with a different column name counting correctly.

Jeff Reisman

@Molly12345 I am assuming your date column being referenced is actually a date-type column and not text/number, correct? Like when you click on a cell in that column the little blue calendar icon shows up on the right side of the cell?

Try this one:

=COUNTIFS({Your Executions planned range}, AND(@cell > TODAY(-8), @cell < TODAY(+1)))

This should count all rows where the date is today or any time in the past 7 days. It's working fine in my test sheet:

Molly12345

Hi David,

Thank you very much for taking the time to try solve this.

It is definitely a date column. I added in extra dummy dates and copied your formula exactly this time. What does at cell mean ?

Many thanks

Molly12345

Hi David !

It worked when I wrote it out instead of copying it. Thank you all so much for you time. I cannot tell you how much I appreciate it!!

Molly12345

David Tutwiler

I'm glad you've got it going!

Molly12345

Hi @David Tutwiler

Can I ask what the inverse of this formula is for due in ....... I have tried many and cannot get the to work for some reason. Also why you put @cell in these formulas ?

=COUNTIFS({planned executions}, >TODAY(), {planned executions}, <TODAY(8))

I also tried =COUNTIFS({planned executions}, >TODAY(), {planned executions}, <TODAY() + 8) but that gives 0 too

Thank you so much in advance :)

David Tutwiler

The @cell is a helper for the formula to know what to compare the argument against (like TODAY()). If you imagine what you do for say an IF statement, you give it a value - say a cell value - and compare it against something else - say another cell value. So the IF statement resolves fairly easily because it is a 1-to-1 comparison. The function

=IF(Status@row = OldStatus@row, true, false)

has only 1 logical relation and is therefore easy to solve.

In the case of COUNTIFS, you are passing a list of values in the first parameter, but then making a seemingly 1-to-1 comparison when you call something like <TODAY(8). Without the @cell specification, the function could think that you're trying to compare the entire list with a value. Putting @cell in the 2nd parameter tells the function that you want to evaluate each individual value in the list on its own. Think of it as starting at the top of the list and evaluating your first value, then moving its way down through your list until it hits the end and counting each value that matches your check. I pasted a screenshot below to help visualize that a little better.

All that being said, I think if you add the @cell back you would still have a problem with both of your functions because of how COUNTIFS works. COUNTIFS has an inherent AND function between your two conditions. Because of that, you are asking it to return a count of how many dates are both in the future AND in the past, which is why you are getting 0. You could solve that pretty quickly by breaking up your functions and adding them together like below:

=COUNTIF({planned executions}, @cell >TODAY()) + COUNTIF({planned executions}, @cell <TODAY(8))

Sorry for the longwinded response, but I hope it helps.