COUNTIFS using current month

David Acord
David Acord ✭✭
edited 11/24/21 in Formulas and Functions

Hi all,


I've been doing some searching on here and can't find the solution to my issue.


I have a smartsheet which has four columns. Three of them may have a name (text/number), the fourth is date only. I'm trying to run a formula that detects the total count of name X from columns 1-3, in the current month.


My formula is as follows, but I keep getting "INCORRECT ARGUMENT SET". I'm thinking it's something to do with the fact there's multiple columns to check the name against AND a date field, but I can't work out what I should do about it.


=COUNTIFS([Developer Assigned Date]:[Developer Assigned Date], IF(ISDATE(@cell), MONTH(@cell)) = MONTH(TODAY()), [Developer Assigned]:[OLT Developer Assigned], "David Acord")


Column 1 = Developer Assigned (text/number)

Column 2 = Secondary Developer Assigned (text/number)

Column 3 = OLT Developer Assigned (text/number)

Column 4 = Developer Assigned Date (date)

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The issue is that your ranges do not match. one range is multiple columns and the other range is a single column (column type does not matter in this particular case). I would suggest a helper column where you join your three text/number columns and then incorporate a FIND/CONTAINS/HAS (whichever best suits you) in your COUNTIFS. My suggestion would be a multi-select dropdown for the Join Column and a HAS function in the formula.

    =COUNTIFS([Developer Assigned Date]:[Developer Assigned Date], IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), [Join Column]:[Join Column], HAS(@cell, "David Acord"))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The issue is that your ranges do not match. one range is multiple columns and the other range is a single column (column type does not matter in this particular case). I would suggest a helper column where you join your three text/number columns and then incorporate a FIND/CONTAINS/HAS (whichever best suits you) in your COUNTIFS. My suggestion would be a multi-select dropdown for the Join Column and a HAS function in the formula.

    =COUNTIFS([Developer Assigned Date]:[Developer Assigned Date], IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), [Join Column]:[Join Column], HAS(@cell, "David Acord"))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • That did it! Thanks so much @Paul Newcome - you do amazing work!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!