Sum if not blank

Heather K

I am trying to sum across multiple columns but have it show blank if the columns do not have values. Each of the columns has a formula in them to calculate a number. However, I want the sum column to show blank instead of 0 if all of the other columns are blank.

My formula right now is =SUMIF([Question 1 Score]@row:[Question 5 Score]@row, <>"", [Question 1 Score]@row:[Question 5 Score]@row) which is still giving me a 0 when all columns are blank.

How do I get it to show blank when the other columns are blank?

Paul Newcome

Try something like this...

 

=IF(SUM([Question 1 Score]@row:[Question 5 Score]@row) > 0, SUM([Question 1 Score]@row:[Question 5 Score]@row))

 

This solution is assuming there will be no negative numbers. There is a solution that would take negatives into account though. If you need it, let me know.

Heather K

That works to show the blank but then if all the scores are 0, it is still showing blank instead of 0.

Paul Newcome

Ok. Give this a whirl:

 

=IF(COUNTIFS([Question 1 Score]@row:[Question 5 Score]@row, ISNUMBER(@cell)) > 0, SUM([Question 1 Score]@row:[Question 5 Score]@row))

Heather K

Perfect. Thank you very much!

Paul Newcome

Happy to help!