Sum values from a column based on whether another column is empty or not

I'm trying to to sum values from the 'Value' column for a particular 'Reference number., but only if a third column ('Confirmation number') has a value. The actual value in the third column is not relevant, just whether it's been completed (confirmed) or not.

I've got as far as this

=SUMIFS(Value:Value, [Reference number]:[Reference number], "1659000166", [Confirmation number]:[Confirmation number], <>NULL)

The <>NULL is a guess to try to show what I would like to achieve.

Any ideas how I could do this?

Find more posts tagged with

Formulas

Comments

Christian Wells

Hi dfo222,

I recently asked a very similar question....

in this thread. https://community.smartsheet.com/discussion/sheet-summary-row-countifs-determine-and-dynamic-references

The answer was to create another column to calulate a value for the null cell, then use the value to count....

I hope this helps

dfo222

Hi Christian,

Many thanks, that gave me the push in the right direction. I used one of the proposed solutions which i think perhaps you didn't or couldn't.

I added an extra column and wrote 1 or 0 to it depending on whether the 'Confirmation number' column ISBLANK.