Counting Entries Satisfying a Condition and Within a Certain Month/Year

egold31

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.

egold31

Additionally, how can I create an equation that finds how many times "OFF" occurred in the month January (for example) and the year 2024?

Dan Curl

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)

Jennifer Kurtz

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?