Help with a formula

PKane
PKane ✭✭✭✭
edited 06/08/23 in Formulas and Functions

Hello All,


Getting an unparseable error for this COUNTIFS formula - hoping someone in the community can help me figure out where I am going wrong.

Formula:

=IF(COUNTIFS({{DC UPS Restudy Status}}, "Restudy Needed", {{Core OpCo}}, "NSP", 1) =0, "", COUNTIFS({{DC UPS Restudy Status}}, "Restudy Needed", {{Core OpCo}}, "NSP", 1))

Snip of the sheet:

Ideally, I want the formula to look at the DC UPS Restudy Status column and count them by OPCO.

Answers

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭
    edited 06/08/23

    You don't need the double {{ just make them single ones. And if this formula is being used on the same sheet as the columns you are using in the formula, you should be using [ instead of { .

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • PKane
    PKane ✭✭✭✭

    @Kleerfyre thanks for your help - I removed one of the sets of { to make it single and it changed to INCORRECT ARGUMENT SET now - which is progress, but I still can't see what the issue is with the formula. :(

    Formula updated: =IF(COUNTIFS({DC UPS Restudy Status}, "Restudy Needed", {Core OpCo}, "NSP", 1) = 0, "", COUNTIFS({DC UPS Restudy Status}, "Restudy Needed", {Core OpCo}, "NSP", 1))



    I am using the formula in a separate sheet. Thanks for the heads-up on the [ vs the {. I was unaware of that requirement.

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    COUNTIFS({DC UPS Restudy Status}, "Restudy Needed", {Core OpCo}, "NSP", 1)

    COUNTIFS({DC UPS Restudy Status}, "Restudy Needed", {Core OpCo}, "NSP", 1)


    What does the 1 at the end of each of these two parts of the formula represent? The syntax of COUNTIFS is COUNTIFS( range1 criterion1 [ range2​criterion2​... ])

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭

    Formula updated: =IF(COUNTIFS({DC UPS Restudy Status}, "Restudy Needed", {Core OpCo}, "NSP") = 0, "", COUNTIFS({DC UPS Restudy Status}, "Restudy Needed", {Core OpCo}, "NSP"))


    Try that and see if it works.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭

    @Carson Penticuff it was because he is trying to combine it with an IF statement too. I noticed that on my second read of his formula. The updated formula I posted should fix the issue.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!