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

egold31
egold31 ✭✭
edited 11/13/24 in Formulas and Functions

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

  • egold31
    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?

  • 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
    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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!