Date Range Formula Help
Can someone please help me to create a formula that returns the number of items with a "Permit Expiration Date" between 7/1/20 and 7/31/20 AND has the "360 Photos Have Been Submitted" unchecked? I've tried several "if and" formulas and they didn't work :(
Thank you!!
Best Answer
-
Sorry. Try this:
=COUNTIFS([360 Photos Have Been Submitted]:[360 Photos Have Been Submitted], 0, [Permit Expiration Date]:[Permit Expiration Date], < DATE(2020, 7, 31), [Permit Expiration Date]:[Permit Expiration Date],
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Hi @Justin Tackitt ,
You need to use a COUNTIFS function. The syntax is: COUNTIFS( range1, criterion1,[ range2, criterion2... ])
Your formula would be:
=COUNTIFS([360 Photos Have Been Submitted]:[360 Photos Have Been Submitted], 1, [Permit Expiration Date]:[Permit Expriation Date], < 7/31/20, [Permit Expiration Date]:[Permit Expriation Date], >7/1/20)
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Thank you for your help!!!
I am getting an invalid operation error :(
=COUNTIFS([360 Photos Have Been Submitted]:[360 Photos Have Been Submitted], 0, [Permit Expiration Date]:[Permit Expiration Date], <7 / 31 / 20, [Permit Expiration Date]:[Permit Expiration Date], >7 / 1 / 20)
-
Sorry. Try this:
=COUNTIFS([360 Photos Have Been Submitted]:[360 Photos Have Been Submitted], 0, [Permit Expiration Date]:[Permit Expiration Date], < DATE(2020, 7, 31), [Permit Expiration Date]:[Permit Expiration Date],
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Oh don't be sorry, I'm thankful for the help!!
Your formula was missing the second date range so I added the following to the end of your formula:
>DATE(2020, 7, 1))
It returned a number, but not the correct number. It is counting all items between those date ranges, it's not taking into consideration whether or not the box is checked.
-
I feel dumb now. I was counting the non checked boxes. It works now!
Thank you very much for your help!!!!
Very much appreciated!
-
On more try. Almost there.
=COUNTIFS([360 Photos Have Been Submitted]:[360 Photos Have Been Submitted], =1, [Permit Expiration Date]:[Permit Expiration Date], < DATE(2020, 7, 31), [Permit Expiration Date]:[Permit Expiration Date],>DATE(2020, 7, 1))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Perfect. Happy to help. Thanks for using the Community.
Be Well, Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
You saved the day for me!!! Thank you again, sir!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!