using COUNTIF to find exact numbers.

I've been using COUNTIF to count the number of occurrences in a month.

So i've got a list of 'events' throughout the year, all with different dates.

I'm wanting to count how many are in each month. What I've done is to use the MONTH formula in another column to give a numerical value of the month in 1 - 12.

And then using a formula to count how many 1's, how many 2's etc. This tells me how many occurrences there are in each month.

=COUNTIF({Improve Data Range 2}, FIND("1", @cell) > 0)

However I noticed the total number was getting a bit high, and realised that my formula for finding '1' was also counting the 1 in 10, so was counting all of Octobers occurrences. I guess by this logic it would also count the 1 in 11, and 12.

Is there to make this formula only identify a singular 1, and not pick up a 1 in a bigger number?

Alternatively is there a way to make the MONTH formula spit out a Month name such as 'Jan' 'Feb' rather than a numerical value? That would be a unique value that countif couldn't mix up.


Best Answer


  • David Tutwiler
    David Tutwiler Overachievers Alumni

    Normally, I'm in favor of splitting out functions so they don't get too messy. However, I think you're best bet will be to add the MONTH() function into this function to make sure you get the right month. Something like:

    =COUNTIF({Improve Data Range 3}, MONTH(@cell) = 1)

    This assumes that Improve Data Range 3 is the column that contains the date.

  • Brilliant thanks. Took me a few tried to get it working, but seems to work a treat.

  • I'm actually having some issues with cross referencing the date column from another sheet. I'm using:

    =COUNTIFS({impdate}, <>. MONTH(@cell) = 1)

    it keep returning #INVALID DATA TYPE.

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    Have you tried:

    =COUNTIFS({impdate}, NOT(MONTH(@cell)=1))

    If that doesn't work, you'll want to look at your column type to make sure it's a date format.

  • Thanks,

    It is still coming up with the error. It seems the MONTH function doesn't like empty cells when Cross linked to other sheets. So the empty 10 cells that appear at the bottom of the sheet are causing the error.

    I need a way for it to ignore cells that don't contain dates. A sort of ISDATE = True function, but I'm not sure how to construct that within the formula.

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    You could do:

    =COUNTIFS({impdate}, NOT(MONTH(@cell)=1), {impdate}, NOT(ISBLANK()))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!