COUNTIFS with multiple inputs and dates
I'm trying include a date range with counting the number of applicants within various depts, in certain date ranges, but it's saying incorrect argument set.
=COUNTIFS(DISTINCT([Name of Requestor]:[Name of Requestor], [Submission Date]:[Submission Date], AND(@cell > DATE (2023, 9, 30), @cell <= DATE(2023, 12, 31))))
Similarly with this, trying to add date range to these, with the second count function being tied to a free text where they write in their department if its not in the original "division"; drop down
=COUNT(DISTINCT(Division:Division)) + COUNT(DISTINCT([Please specify]:[Please specify]))
Best Answer
-
I misunderstood the assignment…
=COUNT(DISTINCT(COLLECT( Division:Division, [Submission Date]:[Submission Date], >DATE(2024, 3, 31), [Submission Date]:[Submission Date], <DATE(2024, 6, 31)))) + COUNT(DISTINCT(COLLECT( [Please specify]:[Please specify], [Submission Date]:[Submission Date], >DATE(2024, 3, 31), [Submission Date]:[Submission Date], <DATE(2024, 6, 31))))
As I undestand it now..
10 = COUNT(DISTINCT(COLLECT( Division:Division, [Submission Date]:[Submission Date], >DATE(2024, 3, 31), [Submission Date]:[Submission Date], <DATE(2024, 6, 31))))
add
2 = COUNT(DISTINCT(COLLECT( [Please specify]:[Please specify], [Submission Date]:[Submission Date], >DATE(2024, 3, 31), [Submission Date]:[Submission Date], <DATE(2024, 6, 31))))
...
Answers
-
=COUNTIFS([Name of Requestor]:[Name of Requestor],"Your_Name", [Submission Date]:[Submission Date], > DATE (2023, 9, 30), [Submission Date]:[Submission Date], <= DATE (2023, 12, 31))
I use this template, and just add more [Column]:[Column],"Criteria", as needed.
=COUNTIFS( [Column]:[Column],"Criteria", [Date]:[Date], > DATE (YYYY, MM, DD), [Date]:[Date], <= DATE (YYYY, MM, DD))
...
-
@heyjay it is saying unparseable still :(
-
Does it matter that I am not looking for a certain "name" ? Rather, I want it to count any input that has been entered
-
Yes, Can you try any of these below if they works for you
=COUNTIFS(column:column,NOT(ISBLANK(@cell)),
[Submission Date]:[Submission Date], > DATE (2023, 9, 30),
[Submission Date]:[Submission Date], <= DATE (2023, 12, 31))If you just want to count the dates the falls within your category,
=COUNTIFS( column:column, NOT(ISBLANK(@cell)), [Submission Date]1:[Submission Date]3, >DATE(2023, 9, 30), [Submission Date]1:[Submission Date]3, <DATE(2023, 12, 31))
or
=COUNTIFS( [Submission Date]1:[Submission Date]3, >DATE(2023, 9, 30), [Submission Date]1:[Submission Date]3, <DATE(2023, 12, 31)
...
-
@heyjay unfortunately this format does not work either— still unparseable
-
-
@heyjay
=countifs(Name of Requestor:Name of Requestor, NOT(ISBLANK(@cell ))), [Submission Date]:[Submission Date], >DATE(2023, 9, 30), [Submission Date]:[Submission Date], <=DATE(2023, 12, 31))
-
=COUNTIFS( [Name of Requestor]:[Name of Requestor], NOT(ISBLANK(@cell)), [Submission Date]:[Submission Date], >DATE(2023, 9, 30), [Submission Date]:[Submission Date], <=DATE(2023, 12, 31))
- Missing square brackets for columns with more than 1 word.
- Extra parenthesis after the NOT(ISBLANK.. function.
...
-
@heyjay omg that worked, thank you! I adapted the formula to count for the other function, with the "please specify" column. However, I'm having some issues. It seems to be working, but when I test it, it doesn't seem to be counting distinct responses in both the Division and Please specify columns. I need the count to not duplicate responses, but count them individually. This should be "11" for the [# of Divisions FS24 Q4] summary box.
Here is the current formula:
=COUNTIFS(Division:Division, NOT(ISBLANK(@cell )), [Submission Date]:[Submission Date], >DATE(2024, 3, 31), [Submission Date]:[Submission Date], <=DATE(2024, 6, 31)) + COUNTIFS([Please specify]:[Please specify], NOT(ISBLANK(@cell )), [Submission Date]:[Submission Date], >DATE(2024, 3, 31), [Submission Date]:[Submission Date], <=DATE(2024, 6, 31))@cell
-
@heyjay thank you so much, that worked!
I adapted the formula to try to use it for counting the number of divisions w.n a certain date period, however, it is not capturing distinct values in the "division" and "please specify" columns, but rather just totaling all of them. Thoughts on how to adjust the formula below? It should be returning 12, not 20.
=COUNTIFS(Division:Division, NOT(ISBLANK(@cell )), [Submission Date]:[Submission Date], >DATE(2024, 3, 31), [Submission Date]:[Submission Date], <=DATE(2024, 6, 31)) + COUNTIFS([Please specify]:[Please specify], NOT(ISBLANK(@cell )), [Submission Date]:[Submission Date], >DATE(2024, 3, 31), [Submission Date]:[Submission Date], <=DATE(2024, 6, 31))@cell
-
Looks like we can get away without including the Division since when that is not blank, the date is not also blank. Can you try this.
=COUNTIFS( [Please specify]:[Please specify], NOT(ISBLANK(@cell )), [Submission Date]:[Submission Date], >DATE(2024, 3, 31), [Submission Date]:[Submission Date], <=DATE(2024, 6, 31)
...
-
@heyjay perhaps i'm not understanding correctly, but how would that formula count the distinct entries in the divisions & please specifies columns? I would need the total of both
-
when i try that, it just counts the 2 in the please specify columns
-
I misunderstood the assignment…
=COUNT(DISTINCT(COLLECT( Division:Division, [Submission Date]:[Submission Date], >DATE(2024, 3, 31), [Submission Date]:[Submission Date], <DATE(2024, 6, 31)))) + COUNT(DISTINCT(COLLECT( [Please specify]:[Please specify], [Submission Date]:[Submission Date], >DATE(2024, 3, 31), [Submission Date]:[Submission Date], <DATE(2024, 6, 31))))
As I undestand it now..
10 = COUNT(DISTINCT(COLLECT( Division:Division, [Submission Date]:[Submission Date], >DATE(2024, 3, 31), [Submission Date]:[Submission Date], <DATE(2024, 6, 31))))
add
2 = COUNT(DISTINCT(COLLECT( [Please specify]:[Please specify], [Submission Date]:[Submission Date], >DATE(2024, 3, 31), [Submission Date]:[Submission Date], <DATE(2024, 6, 31))))
...
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 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!