Kirstine ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

Another day, another question to the Community...! 

I'm trying to get a count of all jobs starting in each month. My formula is giving me an #INVALID OPERATION error though. 

=COUNTIFS({Start Date} >= (DATE(2019, 1, 1)), {Start Date}, <=(DATE(2019, 1, 31)))

What am I doing wrong? 



  • Chak Khiam
    Chak Khiam ✭✭✭✭
    edited 06/25/19

    A missing   ,   after the first {Start Date} range?

    >=DATE(2019,1,1) is sufficient. There is no need for additional brackets.


  • Kirstine
    Kirstine ✭✭✭✭✭✭

    Yaaaaaaaaaaas!!!! Thank you! What a plonker! Ha ha! (I was grasping at straws with the extra brackets!)

    Thanks for the sanity check! 


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    As a suggestion... There are a couple of ways to double up on the accuracy to make sure nothing gets missed.


    1. Instead of referencing specific dates, reference the month (and year if needed) specifically.


    >= DATE(2019, 1, 1) and <= DATE(2019, 1, 31)

    can change to

    MONTH(@cell) = 1

    and to reference the year

    YEAR(@cell) = 2019


    2. If you wanted to use specific dates without having to worry about whether there are 28, 29, 30, or 31 days in the month, you can reference the first of next month and subtract 1.


    <= DATE(2019, 1, 31)

    can change to

    <= DATE(2019, 2, 1) - 1


    Both of these methods will ensure that you are covering the entire month without having to worry about a specific number of days.


    A tip for consolidation and organization:


    If you are referencing the same range more than once within a COUNTIFS (or even a SUMIFS for that matter), you can use the AND function along with @cell references to combine the criteria. 


    I personally find this helpful in keeping track of everything that is happening in the more complex formulas.


    You can also leverage this to work an OR function into the formula if you needed that as an option as well.


    =COUNTIFS({Date}, >= DATE(2019, 1, 1), {DATE}, <= DATE(2019, 1, 31))

    can be changed to

    =COUNTIFS({Date}, AND(@cell >= DATE(2019, 1, 1), @cell<= DATE(2019, 1, 31)))


    =COUNTIFS({Date}, AND(@cell >= DATE(2019, 1, 1), @cell<= DATE(2019, 2, 1) - 1))


    =COUNTIFS({Date}, AND(MONTH(@cell) = 1, YEAR(@cell) = 2019))

  • Kirstine
    Kirstine ✭✭✭✭✭✭

    Thanks Paul, this is what I was wanting. I'm using =COUNTIFS ({Start Date}, AND (MONTH(@cell) = 1, YEAR (@cell) = 2019)), but am now getting #INVALID DATA TYPE. 

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    That is because there is most likely at least one cell within the range that is not a date (either blank, text, or number). To account for this, we will work in an IFERROR statement into both the MONTH and YEAR functions to say that if there is a cell without a date in it, use 0 (zero) for the month and year respectively. We use zero because there is no month or year number zero, so it won't inadvertently get counted.


     =COUNTIFS ({Start Date}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2019))

  • Kirstine
    Kirstine ✭✭✭✭✭✭

    Ah OK, yeah the sheet I'm referencing does have blanks! 

    Thanks very much, this now works perfectly. I just need to go through it all changing the month...! 

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Just a quick recommendation...


    If you are displaying the counts in a metrics table like this...







    or like this...

    January           February          March           April


    You can create a helper column or row and reference specific cells containing the month numbers. This will allow dragfilling and keep you from having to edit the formula itself. You can also use a cell reference for the year.


    I usually have mine set up like so...


                         1                    2                3                4

    2019           Jan                Feb           Mar              Apr

    A                   f




    Where the first column is let's just say [Service Type] and the rest of the columns are the same as what is in row two (Jan, Feb, etc.), I would enter the following where you see f and then dragfill both down and over.


    =COUNTIFS({Date}, AND(IFERROR(MONTH(@cell), 0) = Jan$1, IFERROR(YEAR(@cell), 0) = $[Service Type]$1), {Service}, @cell = $[Service Type]@row)


    Note the use of the dollar sign $. This locks in either the column or row reference (whichever comes after it) and let's you essentially override the auto-update feature when dragfilling so that specific column, row, or cell references stay how they need to.


    If you were using the first setup, it could look something like this (column names in bold):


    Number            Month              Count


    1                         Jan                      f

    2                         Feb

    3                         Mar

    4                         Apr


    and the formula could look something like this...


    =COUNTIFS({Date}, AND(IFERROR(MONTH(@cell), 0) = Number@row, IFERROR(YEAR(@cell), 0) = Number$1))


    You could then dragfill down the Count column and hide the Number column to keep the sheet looking clean.


    Just another tip for consistency and accuracy of data.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!