Update type and date range formula

Daniel Barren

Hello - I am looking to add to my formula below I have in place, adding a date range. End result is to get totals by the week date range. =COUNTIF([Update Type]:[Update Type], "Identified New Pursuit")

Genevieve P.

Oh no problem! Yes, you can definitely just create it in the sheet.

You could just do one column, a date column, and type in the "Start Date Range" and "End Date Range" into two cells, and select two dates. I've coloured my cells a bit differently so I know that it's a reference chart. I also added in some $ signs to make sure that when I reference these cells the formula knows it's an Absolute reference:

=COUNTIFS([Update Type]:[Update Type], "Identified New Pursuit", [Date of Update]:[Date of Update], >=$[Date Range]$2, [Date of Update]:[Date of Update], <=$[Date Range]$4)

You can read more about referencing cells in formulas in our Help Center (see here).

That should do it! 😊

Genevieve P.

Hi Daniel,

You'll need to change the COUNTIF to be a plural COUNTIFS. Then you can add in the Date criteria. Try this:

=COUNTIFS([Update Type]:[Update Type], "Identified New Pursuit", [Date of Update]:[Date of Update], >TODAY(-7), [Date of Update]:[Date of Update], <=TODAY())

Here are some Help Center articles I used to build this formula: COUNTIFS function / TODAY function / Formula tips and tricks

This will look for the range between 7 days ago and today. Is this what you meant by "totals by the week"? Let me know if I've misunderstood what you're looking to do and I'm happy to help further!

Cheers,

Genevieve

Daniel Barren

Thank you, Genevieve! I used the above formula but still received an error.

Ideally I would like to be able to enter a date range, example: 02/03/20 - 02/10/20. I also tried utilizing a filter for the "Date of Update" column, but it deletes my first 3 columns, row 1 which adds up the totals. From what I can tell, there is no way to freeze multiple cells in SmartSheet as well?

I appreciate your help!!

Genevieve P.

Hi Daniel,

In this case you would want to have two different dates input somewhere in your sheet so that you can reference them in your formula instead of the TODAY function. I would recommend creating a "Start Date Range" field and an "End Date Range" field in your Sheet Summary so that you can reference these (make sure they are a Date Type of field).

Try this:

=COUNTIFS([Update Type]:[Update Type], "Identified New Pursuit", [Date of Update]:[Date of Update], >= [Start Date Range]#, [Date of Update]:[Date of Update], <=[End Date Range]#)

Then in the future all you have to do is adjust the dates and the formula will automatically re-calculate based on this new information. If you're still getting an error with this formula, please let me know what it is, along with a screen capture of exactly what you put in the cell.

Thanks!

Genevieve

Daniel Barren

I cant thank you enough for your help! Silly question (perhaps) how do you add Date Range in your sheet?

Dan

Genevieve P.

Hi Daniel,

No problem at all! I'm happy to help.

I used the Sheet Summary field in my example. It can be found by clicking the small sheet icon in the menu on the right:

Then create a New Field and make sure it's a Date type of field:

You could also just use a new Date Column in your sheet, but I prefer Sheet Summary as you only need two cells, not an entire column. You can learn more about Sheet Summaries in our Help Center (click here).

Let me know if this works for you!

Cheers,

Genevieve

Daniel Barren

Thank you again! I do not have access under my current membership. If I were to add a date column and change the headers to "Start Week Date" and "End Week Date" would this work with a formula? If so, can you give me an example.

Genevieve P.

Oh no problem! Yes, you can definitely just create it in the sheet.

You could just do one column, a date column, and type in the "Start Date Range" and "End Date Range" into two cells, and select two dates. I've coloured my cells a bit differently so I know that it's a reference chart. I also added in some $ signs to make sure that when I reference these cells the formula knows it's an Absolute reference:

=COUNTIFS([Update Type]:[Update Type], "Identified New Pursuit", [Date of Update]:[Date of Update], >=$[Date Range]$2, [Date of Update]:[Date of Update], <=$[Date Range]$4)

You can read more about referencing cells in formulas in our Help Center (see here).

That should do it! 😊

Daniel Barren

That did it!! Thank you!

Genevieve P.

Wonderful! So glad to hear it.