Formula from 2021 and 2022

Hello,

Can you help me with the formula? I need to count the "Pending" from August 2021 onwards to 2022.

=COUNTIFS({2022 Orders - Carmi Range 1}, "Pending", {2022 Orders - Carmi Range 2}, AND(IFERROR(MONTH(@cell), 0) >= 1, IFERROR(YEAR(@cell), 0) = 2022))

Answers

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭

    I think it's your IFERROR functions that are throwing it off.

    With the IFERROR function, you need to first put in the function, then add a comma to display what you'd like to see if there is an error. Looking at your formula, it doesn't look like you have that in there for your IFERROR functions.

    So it should be something like:

    AND(

    IFERROR(Month(@cell), 0)>=1,  [what you want displayed if there's an error]),

    IFERROR(YEAR(@cell),0)=2022, [what you want displayed if there's an error])

    )


    Why are you utilizing IFERROR here to start with? Does the formula work if you remove it?

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • Devin Lee
    Devin Lee ✭✭✭✭✭

    Hey @Marilen.Navarro103391,

    Try this:

    =COUNT(COLLECT(Status:Status, Status:Status, "Pending", Date:Date, OR(AND(VALUE(LEFT(@cell, 2)) >= 8, VALUE(RIGHT(@cell, 2)) = 21), VALUE(RIGHT(@cell, 2)) > 21)))
    

    Might translate to this with the references

    =COUNT(COLLECT({2022 Orders - Carmi Range 1}, {2022 Orders - Carmi Range 1}, "Pending", {2022 Orders - Carmi Range 2}, OR(AND(VALUE(LEFT(@cell, 2)) >= 8, VALUE(RIGHT(@cell, 2)) = 21), VALUE(RIGHT(@cell, 2)) > 21)))
    


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!