Cross Sheet Formula - Count how many times a date appears within a date range

Good morning,

We have a log sheet that is constantly building on itself for when we communicate with a client. We call it a "touch." I am trying to count how many times we have had a touch per week of the year. I thought this would be simple, but for some reason I am struggling to make this work for me. Hopefully the screenshots help.

My first pass at the formula I am just getting "0" for each row: =COUNTIF({Touch Log - Detail Range 1}, [Week Start]@row:[Week End]@row)


Touch Log - Detail Sheet (Range)

Data Consolidation

Any help would be greatly appreciated!



  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Hi there! You're close!

    First - make sure all of your date columns are listed in the property columns as a date column. Formulas won't work with dates unless you do that.

    Second - you could probably get rid of all of the columns in between Week Start and Week End. Instead, I was able to create a Week Start column, then put this formula in the Week End column:

    =[week start] + 6

    From there, I was able to use the following formula in the Weekly Touch Count column: (Note: I named the range with my Touch Date column {TouchDate}.)

    =COUNTIFS({TouchDate}, >=[Week Start]@row, {TouchDate}, <=[Week End]@row)

    To translate:

    Count if both of the following criteria are true: Touch date is on or after the week start date AND Touch date is on or before the week end date.

    This counts the number of times there is a date during the given week.

    I mocked it up and it seems to work for me, but let me know if you have any trouble.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!