How do I sum a column of IF formulas?

Copper Contributor

I am trying to sum the column of IF formulas but it's returning "0" since technically the column is filled with formulas, not actual numbers to calculate. Any suggestions?

4 Replies
If you can, upload an example of your workbook (if the data is not sensitive), or at least post an example of your IF formula. By chance, is your IF formula returning numbers that are actually text? For example, IF(condition, "1", "")? If the IF formulas are supposed to be returning numbers, then be sure the numbers within the formula don't have quotes around them. Also, for fields that are used in downstream mathematical operations, I like to return 0 instead of "" (you can always apply a custom number format to make the cell appear blank if it contains a zero to clean it up). IF(condition, 1, "") or IF(condition, 1, 0)

@katiebrauitgam1805 

You can use SumProduct. For example you want to calculate values that are above zero in range  A1:A10

=SUMPRODUCT(--(A1:A10>0);A1:A10)

@katiebrauitgam1805 

 

It does not matter if your cell contains a function, the result should be used in calculating the sum. As @JMB17 noted, perhaps your numbers are being stored as text values. Examine the different in results of the following example:

PReagan_0-1600358133063.png

An example of your formula would surely be helpful.

 

@PReagan

the first formula returns text so sum formula cannot return any results.

 

You can check the attached file for a sample on how to use SumProduct wiht condition.