CODEWITHSUNDEEP
excelexcel-formula
user3607371
user3607371

Reputation: 19

How to make excel ignore characters when it counts sum of values in a row that contains the characters

For example, row 1 has A1 = 1, B1 = 2, and C1 = "N/A". I want to count sum of three cells, but without remove the C1 out of the formula.

If I simply use =Sum(A1:C1), the result would be an error. So, how I can solve the problem?

Upvotes: 1

Views: 28141

Answers (2)

Chhabi Acharya
Chhabi Acharya

Reputation: 13

=sum(A1:A5) [for regular cells]
=sum(A1,D1,G1) [while picking up different cells)

Upvotes: 0

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

You can use SUMIF (better choice if your range contains only #N/A error):

=SUMIF(A1:C1,"<>#N/A")

or, alternatively, if your range contains not only #N/A but also other errors:

=SUM(IF(ISNUMBER(A1:C1),A1:C1))

This is an array formula, so type the formula then press CTRL+SHIFT+ENTER. Curly brackets will automatically appear at the start and end of the formula.

Upvotes: 5

Related Questions