Trying to pull data from a cell if all its descendants are 'not blank'
Hi,
Someone here helped us with a formula a while ago but we've come up against certain scenarios where it isn't working and could use some help to modify it.
Here is the spreadsheet we are working with:
The formula in the cell that I've circled pink is:
=IF(COUNTIFS(DESCENDANTS([Invoiced Amount (Less GST/HST)]@row), @cell > 0) = COUNT(DESCENDANTS(Description@row)), [Invoiced Amount (Less GST/HST)]@row, IF(COUNTIFS(CHILDREN([Quote/PO Amount (Less GST/HST)]@row), @cell > 0) = COUNT(CHILDREN(Description@row)), [Quote/PO Amount (Less GST/HST)]@row, [Current Capital Approved Budget]@row)).
This allows the [Working Budget] column to pull the number from the [Invoiced Amount] column only if all of its descendant rows are greater than 0, if not it pulls from the [Quoted/PO Amount] column if all of it's child rows are greater than 0, if not it pulls from the [Current Capital Approved Budget] column.
The problems we've come against is that occassionally we want to be able to write n/a or put a negative number in some of the quoted and invoiced amounts. Is there a way to modify the formula so that it still pulls from the quoted and invoice columns if all it's children/descendants are NOT BLANK or something like that?
Answers
-
Instead of the "> 0" argument inside each of the COUNTIFS, use "is not blank" which is written as "<> """.
Replace
@cell> 0
with
@cell <> ""
(double quotes)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!