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
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!