Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Counting occurrences for each month for this year only

    ✭✭✭✭
    edited 02/27/25 in Formulas and Functions

    I am using the formula below for a trend widget that pulls metrics for each month. This one is for January, but I have the same formula for each month, replacing 1 for january with 2 for february, etc. I need to update this formula so it counts if the month is january and the year is this year. I pulled one of my YTD formula from another sheet and threw a "hail mary" combining them. Any one know how to fix this?

    Original working formula for the month of January:

    =COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFERROR(MONTH(@cell ), 0) = 1)

    My attempt to frankenstein a YTD version :/

    =COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFFERROR (YEAR(@cell), 0) = YEAR(TODAY()), {Sourced Date}, IFERROR(MONTH(@cell ), 0) = 1)

    · Share on FacebookShare on Twitter

    Best Answer

    • Community Champion
      Answer ✓

      Hi @Erica Cole,

      What error is your formula giving? Your formula should work (the month/year order doesn't matter) so either of the below should be valid:

      =COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {Sourced Date}, IFERROR(MONTH(@cell), 0) = 1)

      =COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFERROR(MONTH(@cell), 0) = 1, {Sourced Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

      My initial thought was that the Sourced Date column hadn't been set to date, but this would stop the original formula from working. The only other thing I can think of is if the cross sheet references (if this is a different sheet) aren't set up quite right (one being a column and the other only a selection of cells?), but the error you're getting might shed some more light on this.

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion
      Answer ✓

      Hi @Erica Cole,

      What error is your formula giving? Your formula should work (the month/year order doesn't matter) so either of the below should be valid:

      =COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {Sourced Date}, IFERROR(MONTH(@cell), 0) = 1)

      =COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFERROR(MONTH(@cell), 0) = 1, {Sourced Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

      My initial thought was that the Sourced Date column hadn't been set to date, but this would stop the original formula from working. The only other thing I can think of is if the cross sheet references (if this is a different sheet) aren't set up quite right (one being a column and the other only a selection of cells?), but the error you're getting might shed some more light on this.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      Hi Nick,

      Thank you for responding. I'm getting #UNPARSEABLE with the first formula. But the second formula is working. I had tried it that way, but when it worked, I thought the count was off. Now that I'm manually checking the numbers, I think it's accurate. Thanks!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions