Trying to pull data from a cell if all its descendants are 'not blank'

Tara Factor

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?

Paul Newcome

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)