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!
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
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?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!