Problems with nested IF/COUNTIF statement
![Isabelle](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
Hi, I am trying to summarize in a parent row the information underneath.
In the child rows, information is either inputted as N/A, or as a numeric value (%).
For example, child rows are as follows:
N/A
N/A
N/A
5%
10%
I want the parent row to look at the child rows and determine if they are all N/A, in that case what should be summarized is N/A. If there is a value in one or more of the rows, then the parent row should just average out the rows that have a numerical value (%). In the example above, the parent row should return 7.5% as an average.
Can you help put this formula together please? Thanks!
Comments
-
Yes. We would use a basic IF statement to specify that if all are "N/A", then display "N/A", otherwise display the average of the numerical values.
=IF(COUNTIFS(CHILDREN(), "N/A")
This will count how many are "N/A".
=IF(COUNTIFS(CHILDREN(), "N/A") = COUNT(CHILDREN()),
This compares it to the count of all of the children rows, so we are saying that if the count of "N/A"'s is equal to the count of the children (meaning all are "N/A").
=IF(COUNTIFS(CHILDREN(), "N/A") = COUNT(CHILDREN()), "N/A",
Then display "N/A".
=IF(COUNTIFS(CHILDREN(), "N/A") = COUNT(CHILDREN()), "N/A", AVG(COLLECT(CHILDREN(), CHILDREN(), ISNUMBER(@cell))))
And this last portion says that if the logical statement is not true (meaning there is at least one numerical value), then we want to collect all of the children that are numbers and average them.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives