If(CountIf) Support

Hello - I am trying to count something if it meets the criteria and running into an Invalid operation error.

What I want to do: If the priority in another sheet is NOT "N/A", Count the status in another sheet if it equals "New".

I was trying to do this: =IF({Priority for AAG} = "N/A", =COUNTIF({Status Column} = [Helper Column - Status]@row)) but I don't think I'm completing the formula as I'm not putting in a false value statement mostly because I don't want it to count it.

What am I missing?

Tags:

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭

    @Bria Berger

    It looks like you have some characters in your Countif portion of your formula that do not belong,

    This is the structure: COUNTIF(RANGE:RANGE,CRITERION)

    Also when embedding Formulas inside formulas, you would not use an = symbol to start the nested formula.

    For your Statement try this:

    =IF({Priority for AAG} = "N/A", COUNTIF({Status Column},[Helper Column - Status]@row))

  • Bria Berger
    Bria Berger ✭✭✭✭

    @JamesB Thanks for the help and clarity on the embedded formulas. I'm still getting the error using what you dropped in. Is there something I need to put in that accounts for the false value for the if portion of the formula? Or the countif portion for that matter?

    =IF({Priority for AAG} = "N/A", COUNTIF({Status Column}, ([Helper Column - Status]@row)))

  • JamesB
    JamesB ✭✭✭✭✭✭

    @Bria Berger If a false value is not defined the cell will be blank.

    I notice that you are specifically looking for N/A on your reference sheet. Is the N/A on your reference sheet being generated by a formula that is erroring or is the N/A being manually put in?

    One thing about formulas is that if the column/data being referenced has errored data being created by another formula it will cause your other formula to fail. I hope this makes sense.

  • Bria Berger
    Bria Berger ✭✭✭✭

    It does make sense. N/A is an option from a drop down list (multi select disabled). @JamesB

  • JamesB
    JamesB ✭✭✭✭✭✭

    @Bria Berger

    After looking at your formula in more depth, I wanted to make sure I am giving you the best solution to what your are trying to do.

    I created a sheet with the scenario above and I am not getting an error in my formula, so as long as your sheet reference data is correctly selected the formula should work.

    However, that being said, looking at your formula it seems like you want to count all rows where the Priority is N/A and the Status is equal to a status on your main sheet. If I am correct, then you want to use a countifs for this result.

    =COUNTIFS({Priority for AAG},"N/A",{Status Column},[Helper Column - Status]@row)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!