Stuck on completed in the last 7 days formula / function
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 :)
Best Answers
-
@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))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
I'm glad you've got it going!
Answers
-
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))
-
@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))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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 😊
-
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).
-
Thank you so much for your insight ! I am new to SS so this is a great help.Will try this and report back 😀
-
No problem, I hope it helps.
-
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 😀
-
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.
-
@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:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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
-
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!!
-
I'm glad you've got it going!
-
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 :)
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives