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

Billy Rock

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

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.

Best,

Heather