Using CountIfs for months and year

Options

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?

below is a picture from my main tracking sheet:

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/17/24
    Options

    @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.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!