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.

    Using CountIfs for months and year

    ✭✭
    in Formulas and Functions

    Hi,. I am struggling with my formula to CountIfs. Currently, I just have a COUNTIF formula in there to count from a reference sheet, the number of requests that came in by month only. I need to update this to be COUNTIFS though so that I can add in the year, so that I can see totals for each month/year vs just all of december for example. The numbers showing below for Nov/Dec are the total for both 2023 and 2024 but I need them to be distinct. Any thoughts?

    image.png

    below is a picture from my main tracking sheet:

    image.png
    · Share on FacebookShare on Twitter

    Answers

    • Community Champion
      edited 05/17/24

      @Hawk23

      Two things. I would recommend In your month submitted Column to Do this formula.

      =IF(MONTH([Created Date]@row) = 1, "January", IF(MONTH([Created Date]@row) = 2, "February", IF(MONTH([Created Date]@row) = 3, "March", IF(MONTH([Created Date]@row) = 4, "April", IF(MONTH([Created Date]@row) = 5, "May", IF(MONTH([Created Date]@row) = 6, "June", IF(MONTH([Created Date]@row) = 7, "July", IF(MONTH([Created Date]@row) = 8, "August", IF(MONTH([Created Date]@row) = 9, "September", IF(MONTH([Created Date]@row) = 10, "October", IF(MONTH([Created Date]@row) = 11, "November", IF(MONTH([Created Date]@row) = 12, "December"))))))))))))

      What this does is change the number to the month name. It will then make it so your count if formula can be dragged down instead of changing it for each row in a year.

      Or Create a Helper column that is just =IF([Month Submitted]@row=1,"January"……..

      You could also add a hidden "Helper column" to put the year in on the sheet your pulling data to. At which point You wont have to change the formula at all and just copy it to the rows you need.

      That said Here is the formula you want.

      =Countifs({Month Submitted Ref},[Month column]@row,{Year Submitted Ref},[Year Column]@row

      otherwise

      =Countifs({Month Submitted Ref},1,{Year Submitted Ref},2023)

      =Countifs({Month Submitted Ref},2,{Year Submitted Ref},2023)

      =Countifs({Month Submitted Ref},3,{Year Submitted Ref},2023)

      =Countifs({Month Submitted Ref},1,{Year Submitted Ref},2024)

      =Countifs({Month Submitted Ref},2,{Year Submitted Ref},2024)

      =Countifs({Month Submitted Ref},3,{Year Submitted Ref},2024)

      So on and so forth

      If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

      · Share on FacebookShare on Twitter
    • Community Champion

      Hi @Hawk23,

      If you have helper columns for year/month in your first sheet (the sheet for the totals), you don't need them in the data sheet and can use the Created column for both. For example, with a Month Number column and a Year Column in the first sheet you could then use a formula like this as a column formula:

      =COUNTIFS({Created}, AND(IFERROR(MONTH(@cell) = [Month Number]@row, 0), IFERROR(YEAR(@cell) = Year@row, 0)))

      Alternatively you could do a COUNTIFS without the helper columns, but you would then need to tailor each row. For example, Jan 2024 would be:

      =COUNTIFS({Month Submitted},(IFERROR(MONTH(@cell) = 1), {Year Submitted},IFERROR(YEAR(@cell) = 2024))

      This would entail a bit more manual work though - even a month number column alone would make it much easier to do.

      Hope this helps, but if I've misunderstood anything or you've any problems/questions then just ask!

      · 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