Counting Entries Satisfying a Condition and Within a Certain Month/Year
Hello Everyone!
I am trying to create a formula that counts the number of times a condition is satisfied for each month/year. My sheet has a column "Parameter" that either has the value "OFF" or "ON". I would like to create a formula that counts the number of times "OFF" occurs per month. However, the dates in my data sheet are formatted "13-Nov-2024" for example.
How can I create a formula that counts the numbers of times "OFF" occurs every month/year? I greatly appreciate the help.
Answers
-
Additionally, how can I create an equation that finds how many times "OFF" occurred in the month January (for example) and the year 2024?
-
In your sheet create four columns
1. MonthText
2. Month
3. Year
4. Date Value
Month Text will be a formula =MID(Date@row,4,3)Month is a big one.
=IF(MonthText@row = "Jan", 1,
IF(MonthText@row = "Feb", 2,
IF(MonthText@row = "Mar", 3,
IF(MonthText@row = "Apr", 4,
IF(MonthText@row = "May", 5,
IF(MonthText@row = "Jun", 6,
IF(MonthText@row = "Jul", 7,
IF(MonthText@row = "Aug", 8,
IF(MonthText@row = "Sep", 9,
IF(MonthText@row = "Oct", 10,
IF(MonthText@row = "Nov", 11,
IF(MonthText@row = "Dec", 12,0))))))))))))Year is =VALUE(RIGHT(Date@row,4)) Date Value is =DATE(Year@row, Month@row, VALUE(LEFT(Date@row, FIND("-", Date@row) - 1)))
You may not need this one but it is nice to have.
For your counting you would use COUNTIFS Here is an example where you are finding number of rows that were ON in Feb 2024 =COUNTIFS([Primary Column]:[Primary Column], Parameter:Parameter, "ON", Year:Year, 2024, Month:Month, 2)
-
Are your dates always entered as DD-MM-YYYY? (There are ways to convert that kind of text to a date format, but I don't think you'd need to do that if your dates are always formatted the same way.)
You could use the COUNTIFS function in this case, I think. Additionally, the "MID" function can help you evaluate whether "Nov" appears in your date cell. For example:
=COUNTIFS(Parameter:Parameter, "Off", MID(@cell, 4, 3 ) = "Nov")
If you also wanted to look for the year, you could add another condition. And since the Year appears at the right end of your date format, you could use the RIGHT function:
=COUNTIFS(Parameter:Parameter, "Off", Date:Date, MID(@cell, 4, 3 ) = "Nov", Date:Date, RIGHT(@cell, 4)="2024")
Does that help?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!