CODEWITHSUNDEEP
excelformula
user3340043
user3340043

Reputation: 11

Excel doesn't count zero value of cell

Trying to discount zero value in Excel.

My formula is =SUM(U19+X19)/2

however if either U19 or X19 contain a value of zero I want the formula to be =SUM(U19+X19)/1 or =SUM(U19+X19)

Upvotes: 1

Views: 125

Answers (2)

mechanical_meat
mechanical_meat

Reputation: 169364

You could use COUNTIF. You would need to call it twice since it does not accept disjoint cells.

=(U19 + X19) / (COUNTIF(U19,"<>0") + COUNTIF(X19,"<>0"))

Upvotes: 0

fliedonion
fliedonion

Reputation: 952

How about:

=IF(U19*X19=0, SUM(U19+X19), SUM(U19+X19)/2)

or more simple:

=IF(U19*X19=0, U19+X19, (U19+X19)/2)

Upvotes: 3

Related Questions