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)))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!