Help with Formula: COUNTIFS using date range and single dropdown data
I'm trying to figure out a formula to count how many projects occur within a specific date range as well as have one of three different payment status'.
Here's my working date range formula. I'm trying to AND the date range with the three OR'd payment status'.
=COUNTIF({EISD}, AND(@cell >= DATE(2021, 5, 1), @cell <= DATE(2021, 5, 31)))
My new formula is looking like this so far. I haven't been able to locate a manual or training materials or live person for our Enterprise service so you guys are my last shot. The coding is still bizarre to me. I'm sure it's like this to avoid lawsuits.
=COUNTIFS(AND(Payment Status:Payment Status, OR(@cell = "Initial Payment", @cell = "Fully Paid", @cell = "100% In-Service")),EISD:EISD, AND(@cell >= DATE(2021, 5, 1), @cell <= DATE(2021, 5, 31)))
Here's a description of how it needs to behave:
Output: Count of projects that (fall within a specific date range) AND (have a payment status of "Initial Payment" OR "Fully Paid" OR "100% In-Service")
The EISD column is a Date formatted column. The Payment Status column is a Dropdown (Single Select). Both are on the same sheet.
Best Answer
-
It was very close. I tweaked the column references and added one ) at the end and it worked. Thanks for the help!
Here's the final code.
=COUNTIFS({Payment Status}, OR(@cell = "Initial Payment", @cell = "Fully Paid", @cell = "100% In-Service"), {EISD}, AND(@cell >= DATE(2021, 5, 1), @cell <= DATE(2021, 5, 31)))
I don't know if the column references for mine are special because I'm working in a metrics sheet or what but I tested it and it's working.
Answers
-
Can you break apart the two outputs and get them to work in different columns, and then use an AND formula using the two columns as the source, and then if that still works, substitute the formula into the column references?
Because this looks wrong:
AND(Payment Status:Payment Status, OR(@cell = "Initial Payment", @cell = "Fully Paid", @cell = "100% In-Service"))
it's the Payment Status:Payment Status that is sticking out as wrong, because that does not resolve to a string
-
So I've been able to get two individual counts to work. I just need to AND them together.
=COUNTIF({EISD}, AND(@cell >= DATE(2021, 5, 1), @cell <= DATE(2021, 5, 31)))
=COUNTIF({Payment Status}, OR(@cell = "Initial Payment", @cell = "Fully Paid", @cell = "100% In-Service"))
Both of these work correctly by themselves.
-
Try this. =Countifs assumes the AND when you list several different criteria. So the initial AND was superfluous.
=COUNTIFS(Payment Status:Payment Status, OR(@cell = "Initial Payment", @cell = "Fully Paid", @cell = "100% In-Service"),EISD:EISD, AND(@cell >= DATE(2021, 5, 1), @cell <= DATE(2021, 5, 31))
Does this rendition work? It should count Any Payment status of Initial, Fully, or 100%, and between those two dates.
-
It was very close. I tweaked the column references and added one ) at the end and it worked. Thanks for the help!
Here's the final code.
=COUNTIFS({Payment Status}, OR(@cell = "Initial Payment", @cell = "Fully Paid", @cell = "100% In-Service"), {EISD}, AND(@cell >= DATE(2021, 5, 1), @cell <= DATE(2021, 5, 31)))
I don't know if the column references for mine are special because I'm working in a metrics sheet or what but I tested it and it's working.
-
The column names for yours are special because you are referencing external style sheets. Yes, I see my missing parenthesis. Nice catch. I'll update my answer too.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62K Get Help
- 348 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 132 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!