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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!