Update type and date range formula

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")


Best Answer

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • 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.
    Genevieve P. Employee Admin
    edited 02/10/20

    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

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

    Dan

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • 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.

  • That did it!! Thank you!

  • Genevieve P.
    Genevieve P. Employee Admin

    Wonderful! So glad to hear it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!