Countif with Dates and Criteria

Lynne Thomas

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!

Genevieve P.

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

Lynne Thomas

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.

Countifs.png

Genevieve P.

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?