Adding a date range to COUNTIFS
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!
Answers
-
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)))
-
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.
-
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!