Problems with nested IF/COUNTIF statement
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
- 63.7K Get Help
- 405 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives