COUNTIFS with multiple inputs and dates

Btrombler
Btrombler ✭✭
in Formulas and Functions
image.png

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:
· Share on FacebookShare on Twitter

Best Answer

  • heyjay
    heyjay ✭✭✭✭✭
    edited 07/26/24 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))))

    ...

    · Share on FacebookShare on Twitter
«1

Answers

  • heyjay
    heyjay ✭✭✭✭✭
    edited 07/25/24 

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

    ...

    · Share on FacebookShare on Twitter
  • Btrombler
    Btrombler ✭✭

    @heyjay it is saying unparseable still :(

    · Share on FacebookShare on Twitter
  • Btrombler
    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

    · Share on FacebookShare on Twitter
  • heyjay
    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))

    or

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

    ...

    · Share on FacebookShare on Twitter
  • Btrombler
    Btrombler ✭✭

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

    · Share on FacebookShare on Twitter
  • heyjay
    heyjay ✭✭✭✭✭

    Mind sharing a snip of your sheet and or formula?

    ...

    · Share on FacebookShare on Twitter
  • Btrombler
    Btrombler ✭✭

    @heyjay

    image.png

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

    · Share on FacebookShare on Twitter
  • Btrombler
    Btrombler ✭✭

    @heyjay

    · Share on FacebookShare on Twitter
  • heyjay
    heyjay ✭✭✭✭✭
    edited 07/25/24 
    =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.

    ...

    · Share on FacebookShare on Twitter
  • Btrombler
    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.

    image.png

    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

    · Share on FacebookShare on Twitter
  • Btrombler
    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.

    =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

    image.png

    · Share on FacebookShare on Twitter
  • heyjay
    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)

    ...

    · Share on FacebookShare on Twitter
  • Btrombler
    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

    · Share on FacebookShare on Twitter
  • Btrombler
    Btrombler ✭✭

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

    · Share on FacebookShare on Twitter
  • heyjay
    heyjay ✭✭✭✭✭
    edited 07/26/24 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))))

    ...

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!