0

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)

2 Answers 2

2

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.

enter image description here

2
  • 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.
    – Kelly
    Feb 22, 2021 at 20:16
  • 1
    Good to hear. If this solves your issue, then please consider marking as an approved answer.
    – Isolated
    Feb 22, 2021 at 20:19
0

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

enter image description here

one zero

enter image description here

3
  • 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.
    – Kelly
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.