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]))

Find more posts tagged with

Countif

Accepted answers

heyjay

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))))

All comments

heyjay

=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))

Btrombler

@heyjay it is saying unparseable still :(

Btrombler

Does it matter that I am not looking for a certain "name" ? Rather, I want it to count any input that has been entered

heyjay

https://community.smartsheet.com/discussion/comment/446371#Comment_446371

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))

=COUNTIFS(
[Submission Date]1:[Submission Date]3, >DATE(2023, 9, 30),
[Submission Date]1:[Submission Date]3, <DATE(2023, 12, 31)

Btrombler

@heyjay unfortunately this format does not work either— still unparseable

heyjay

Mind sharing a snip of your sheet and or formula?

Btrombler

@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))

Btrombler

@heyjay

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))

Missing square brackets for columns with more than 1 word.
Extra parenthesis after the NOT(ISBLANK.. function.

Btrombler

@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

Btrombler

@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.

heyjay

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)

Btrombler

@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

Btrombler

when i try that, it just counts the 2 in the please specify columns

heyjay

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))))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.