# IF statement pertaining to a SUM formula

I have created a spreadsheet that has a series of questions and an applicable drop down list beside each question with an appropriate numeric rating (e.g. 0, 1, 2, 3, 4 or 5). I have put in a formula to sum the total of all of those numbers. I would also like to add an additional statement to say that if any of those cells in the sum formula are equal to zero then the total value should equal zero.

Here is my current formula:

``````=SUM(B16,F16,D16,H16,J16,L16,N16,B29,D29,F29,H29,J29,L29,N29)
``````

You could wrap a product formula in the IF statement, like this.

``````=IF(PRODUCT(A4,B4,C4,D4,E4,F4,G4)=0,0,SUM(A4,B4,C4,D4,E4,F4,G4))
``````

So, when multiplying the cells and if they equal 0, then show 0, else sum the cells. Just adjust the cell reference from this formula.

• Yes! This worked. Thank-you!! I was trying very similar things but did not have the PRODUCT as part of it. That looks to be the key. Much appreciated. Feb 22, 2021 at 20:16
• Good to hear. If this solves your issue, then please consider marking as an approved answer. Feb 22, 2021 at 20:19

If you have Office 365:

``````=LET(x,INDEX(\$B\$16:\$N\$29,{1;14},{1,3,5,7,9,11,13}),IF(OR(x=0),0,SUM(x)))
``````

If you have an earlier version, you may be able to use:

``````=IF(OR(INDEX(\$B\$16:\$N\$29,{1;14},{1,3,5,7,9,11,13})=0),0,SUM(INDEX(\$B\$16:\$N\$29,{1;14},{1,3,5,7,9,11,13})))
``````

And, as suggested by @ScottCraner, if you have an even earlier edition of Excel, you may need to use:

``````=IF(OR(INDEX(\$B\$16:\$N\$29,N(IF({1},{1;14})),N(IF({1},{1,3,5,7,9,11,13})))=0),0,SUM(INDEX(\$B\$16:\$N\$29,N(IF({1},{1;14})),N(IF({1},{1,3,5,7,9,11,13})))))
``````

entered as an array formula by holding down ctrl + shift while hitting enter

• `INDEX(\$B\$16:\$N\$29,{1;14},{1,3,5,7,9,11,13})` returns an array of the values in the cells you indicate you want to SUM
• We can't use `COUNTIF` as that requires range argument, but we can use the `OR` statement to see if any entries are equal to zero.

no zeros

one zero

• Thank-you. I've tried the 2nd formula you have suggested (I don't have Office 365) and it's almost right but it doesn't seem to include any values after cell B16. Feb 22, 2021 at 20:10
• change the indexes to `INDEX(\$B\$16:\$N\$29,N(IF({1},{1;14})),N(IF({1},{1,3,5,7,9,11,13})))` and make sure to array enter them with Ctrl-Shift-Enter instead of Enter when exiting edit mode. @Kelly Feb 22, 2021 at 22:04
• @Kelly Try Scott Craner suggestion Feb 22, 2021 at 23:54