How do I count the number of times dates appear within a range?

Tara M

Hi,

I am trying to track the number of times a call occurs within a date range in a cross sheet reference formula. The user types the date on the call log and I would like to use the dates as a way to measure how many times calls occur within a week, month, and project to date

Screenshot 2020-04-30 at 15.05.50.png

.

I have tried different ways, but I am stuck on such a simple thing!

=COUNT({WOB's General Call Log Range 1}(AND(@cell >= CALL DATE(2020, 04, 26), @cell <= DATE(2020, 05, 2))))

I also used COUNTIF and COUNTIFS and still received an error message.

Thank you so much,

Tara

Jon Baier

Try this

=COUNTIFS({WOB's General Call Log Range 1}, <=(DATE(2020, 5, 2)), {WOB's General Call Log Range 1}, >=(DATE(2020, 4, 26)))

Tara M

Thank you so much for commenting, for some reason this didn't work either.

benhamilton1

Has anyone ever solved this?

Genevieve P.

Hi @benhamilton1

Can you post screen captures of your current set-up, along with a description of what you're looking to achieve and the formula you've tried? (But please block out sensitive data)

The structure above should work as long as the column is a Date type of column:

=COUNTIFS({Date Column}, <= DATE(yyyy, mm, dd), {Date Column}, >=DATE(yyyy, mm, dd))

Cheers,

Genevieve

benhamilton1

Genevieve,

The date is a precise date & time creating an unparseable error regardless of the formula.

Genevieve P.

Hi @benhamilton1

Can you post the formula you're using?

The Created column is a System column so it's a bit different than a regular date column, but the formula should still work.

Keep in mind that the first date you identify is the highest date, since you're looking for less than that date. Then the second one is the earliest date, as you're looking for greater than that date. Does that make sense?

=COUNTIFS({Date Column}, <= DATE(yyyy, mm, dd), {Date Column}, >=DATE(yyyy, mm, dd))

Cheers,

Genevieve

benhamilton1

=COUNTIFS({Other Sheet Range 1}, ">=" & DATE(2023,3,1), {Other Sheet Range 1}, "<=" & DATE(2023,3,31), {Other Sheet Range 2}, "<>")

Genevieve P.

Hi @benhamilton1

Thanks for posting, this helps a lot!

1) We'll want to jump right into the >= operators without any quotes. The quotes turn it into text to search for, instead of instructions in the formula.

2) We'll want to remove the & symbol and go right into the DATE() instruction.

3) Can you clarify what you want in the last range? Is "<>" what you're searching for, or are you saying "not equals"... something?

Try:

=COUNTIFS({Other Sheet Range 1}, >=DATE(2023,3,1), {Other Sheet Range 1}, <=DATE(2023,3,31), {Other Sheet Range 2}, "<>")

or

=COUNTIFS({Other Sheet Range 1}, >=DATE(2023,3,1), {Other Sheet Range 1}, <=DATE(2023,3,31), {Other Sheet Range 2}, <> "")

If you meant to search for "not blank".

Cheers,

Genevieve

benhamilton1

That one works, thank you!

Genevieve P.

Awesome, I'm glad I could help!