COUNTIFS QUESTION

I need to count a contract only if the date when it was reviewed was during Q2 , but the contract can be review several times, so in my database I have a date column for every time I had to reviewed it. So I need a formula that count if revision date 1 its in Q2, but also if revision date 2 its in Q2.

Q2 its between : DATE(2021, 8, 1) DATE(2021, 10, 31)

Currently using this but is not working yet:

=COUNTIFS([Fecha Respuesta Legal 1]@row:[Fecha Respuesta Legal 1]@row, >=DATE(2021, 8, 1), [Fecha Respuesta Legal 1]@row:[Fecha Respuesta Legal 1]@row, <=DATE(2021, 10, 31))+COUNTIFS ([Fecha respuesta Legal 2]@row:[Fecha respuesta Legal 2]@row, >=DATE(2021, 8, 1), [Fecha respuesta Legal 2]@row:[Fecha respuesta Legal 2]@row, <=DATE(2021, 10, 31))

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hello @Kathe

    Your formula was on track - you have a syntax error when writing your ranges.

    =COUNTIFS([Fecha Respuesta Legal 1]:[Fecha Respuesta Legal 1], >=DATE(2021, 8, 1), [Fecha Respuesta Legal 1]:[Fecha Respuesta Legal 1], <=DATE(2021, 10, 31))+COUNTIFS ([Fecha respuesta Legal 2]:[Fecha respuesta Legal 2], >=DATE(2021, 8, 1), [Fecha respuesta Legal 2]:[Fecha respuesta Legal 2], <=DATE(2021, 10, 31))

    Kelly

  • Hello Kelly,

    Actually the ranges are correct, because when I divided the formula and included only the first part it is working: =COUNTIFS([Fecha Respuesta Legal 1]@row:[Fecha Respuesta Legal 1]@row, >=DATE(2021, 8, 1), [Fecha Respuesta Legal 1]@row:[Fecha Respuesta Legal 1]@row, <=DATE(2021, 10, 31))

    I think the problem is with the second part with the +COUNTIFS.....

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Kathe

    I'm sorry - somehow your response was lost until just now. And you're right - I don't see now in the ranges what I thought I saw then. Did you get this working?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!