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?
Answers
-
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))
-
@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)))
-
@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.
-
It does make sense. N/A is an option from a drop down list (multi select disabled). @JamesB
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!