Does it matter that I am not looking for a certain "name" ? Rather, I want it to count any input that has been entered
COUNTIFS with multiple inputs and dates
Btrombler
✭✭✭
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]))
Tags:
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
- Customer Resources
- 66.7K Get Help
- 436 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
- Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
- I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …