Adding a date range to COUNTIFS

Nat

Hello,

I need to add a date range to a rollup I created for tracking sales meetings:

=COUNTIFS({source sheet customer name}, $[Primary Column]@row, {source sheet dropdown result}, [In Person Meeting]$1)

The above works, but is obviously counting the whole sheet, which includes 2 years of data.

I tried:

=COUNTIFS({source sheet customer name range}, $[Primary Column]@row, {source sheet dropdown range}, [In Person Meeting]$1, {source sheet date range}, >23 / 4 / 1 < 24 / 3 / 31)

but that gives me the answer "0" for all of them.

If anyone could help I'd appreciate it!

Paul Newcome

When referencing a date in a formula, you have to use a DATE function.

23 / 4 / 1

is basically saying 23 divided by 4 divided by 1.

Give this a try instead:

=COUNTIFS({source sheet customer name range}, $[Primary Column]@row, {source sheet dropdown range}, [In Person Meeting]$1, {source sheet date range}, AND(@cell> DATE(2023, 04, 01), @cell< DATE(2024, 03, 31)))

Nat

Hi @Paul Newcome

oops! I guess I didn't learn that one!

This worked and is much appreciated as always.

I'll try to find a formulas training. It always feels as though I'm closing my eyes and hoping the back of my mind remembers something, rather than really understanding them.

Nat

Paul Newcome

Happy to help. 👍️

Check out the Formula Handbook Template. It provides an interactive experience with various formulas and functions. If you really mess it up and can't salvage it, you can always download it again and start over.

https://app.smartsheet.com/b/publish?EQBCT=32f8257f5d1948bfa948da170231a0c5&_gl=1*ilz9of*_ga*MTYwMTgwMzM1Mi4xNjg0NDM4Mzg5*_ga_ZYH7XNXMZK*MTY5MjI5NDU5Ny4yNjMuMS4xNjkyMjk0Njg0LjM0LjAuMA..&_ga=2.176441837.916571162.1692012769-1601803352.1684438389