Countif with Dates and Criteria

I am trying to do a count of a symbol (red dot) by each month. I have a column for the month "Date" and four columns (Missing Mentoring Form, Missing Training Course, SharePoint Error, Skillset Does Not Appear on Training Matrix). When there user selects a drop down to red i would like it to be counted. I have that running as a Countif. I need also to add the counts for each by month. Help!

Tags:

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Lynne,

    If I understand you correctly, you want a COUNT based on two criteria: that the Status is Red and that the Month is a Specific Month, is that correct?

    If so, we can use a plural COUNTIFS formula for this! I am guessing your red dot column is called "Status", but if it's something different you'll need to swap that out.

    The MONTH function can find a specific Month, listing them by numbers, so 1 is January, 2 is February, etc. We'll wrap the MONTH function in an IFERROR function as well, to make sure it only focusses on the dates present.


    Here's a count of Red Dots that are in the month of January:

    =COUNTIFS(Status:Status, "Red", Date:Date, IFERROR(MONTH(@cell), 0) = 1)


    For a count in February, you just need to change the 1 at the end to be 2:

    =COUNTIFS(Status:Status, "Red", Date:Date, IFERROR(MONTH(@cell), 0) = 2)


    Does that make sense? Let me know if you need any more help with this. A screen capture of your current sheet/set up would be helpful, but please block out any sensitive data.

    Cheers!

    Genevieve

  • Hi Genevieve,

    Thank you for responding. Here is a copy of my sheet so far. It is a working draft so no sensitive data. Where the dots are i would like to keep track of each of those by the month that is listed in the Date column. Thank you.


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Lynne Thomas

    Thank you for providing this information! Now that I've seen your set up, I actually have a different suggestion. Although there is a way to build this all into one formula, I think the easiest/clearest thing to do would be to have two separate formulas.

    The first would be put in a "helper column" that you can hide in the sheet - this would purely be to calculate how many red dots there are per row. It would be a COUNT formula that returns a number for each row.

    Then you can use a second formula to SUM that helper column, based on the month in your Date column. (NOTE: This presumes that your Date column is a Date-Type of column. Is that correct?)

    The reason I'm breaking this up is I think it will be easier for you to manage and adjust should there be changes in the future.


    Here's the first formula:

    =COUNTIF([Missing Mentoring Form]@row:Resolved@row, "Red")

    It's looking in the range from one column across that row to the end, to "Resolved". You can then drag-fill this down the column to return the number of Red balls in each row.


    Then here's the second formula, for January:

    =SUMIF(Date:Date, IFERROR(MONTH(@cell), 0) = 1, [Helper Column]:[Helper Column])

    February:

    =SUMIF(Date:Date, IFERROR(MONTH(@cell), 0) = 2, [Helper Column]:[Helper Column])

    March:

    =SUMIF(Date:Date, IFERROR(MONTH(@cell), 0) = 3, [Helper Column]:[Helper Column])

    April:

    =SUMIF(Date:Date, IFERROR(MONTH(@cell), 0) = 4, [Helper Column]:[Helper Column])

    May:

    =SUMIF(Date:Date, IFERROR(MONTH(@cell), 0) = 5, [Helper Column]:[Helper Column])

    June:

    =SUMIF(Date:Date, IFERROR(MONTH(@cell), 0) = 6, [Helper Column]:[Helper Column])


    ... etc.

    Does that make sense?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!