1

There are four cells a1,b1,c1,d1. a1,b1,c1 holds a number and d1 is for the sum of them.

Sometimes a cell may fill with an asterisk(*) when there is no number for that category so I use the asterisk. d1 has a formula =sum(a1:c1), but the result is !value# because of some cells filled by the asterisk.

How should I write my formula to count asterisk(*) as zero in sum calculate? enter image description here

Improve this question
4
  • 2
    this is strange since excel sum formula automatically neglects * or an empty cell. I tried it in my office 16 and the formula worked correctly without any error
    – MSM
    Commented Feb 5, 2018 at 17:37
  • show your data, show your formulas. Without that we are only guessing at what you are doing or describing incorrectly.
    – Ron Rosenfeld
    Commented Feb 5, 2018 at 17:54
  • @Ron Rosenfeld ok i have added image
    – sajad
    Commented Feb 5, 2018 at 18:11
  • @M Shajeeh Mustafa see image
    – sajad
    Commented Feb 5, 2018 at 18:11

3 Answers 3

Reset to default
3

Your picture doesn't match the formula given in your question. The formula in the question doesn't have the problem you are asking about:

=SUM(A1:C1)

will return the sum of any numbers in the range A1:C1. The SUM function treats any text values as if they were zero).

However, the formula in the picture returns an error because P1*150 returns an error if P1 is not numeric. If you want to use the SUM function to treat text as zero, you have to apply it to the cell value before doing any arithmetic with it. To allow for the possibility of any of the cells being text, replace the formula with:

=SUM(O2)*100+SUM(P2)*150+SUM(Q2)*200
Improve this answer
1
  • sorry i have moved my table near formula section for take a image and send to site. your solution worked like a charm, thanks alot
    – sajad
    Commented Feb 6, 2018 at 8:18
2

Use row 2 to parse row 1. For example, cell A2 would read =IF(ISNUMBER(A1),A1,0). This will replace non-numbers with 0's in row 2. Sum row 2.

Improve this answer
2

Your formula is not SUMing the three cells. In other words, it is NOT the same =sum(A1:D1). Rather you are multiplying and summing the products, hence your error result.

Try (changing the cell reference to those cells you wish to process)

=SUM(IF(ISNUMBER(B2:D2),B2:D2)*{100,150,200})

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

IF you do that correctly, Excel will place braces {...}around the formula.

Improve this answer
2
  • unfortunately your solution return zero.
    – sajad
    Commented Feb 6, 2018 at 8:22
  • @sajad That will happen if you do not enter the formula with the CSE keys as described in my answer, or if the values in the referenced cells are not numbers. If you examine the formula bar when you have selected the cell, but have NOT placed the cursor in the formula bar, do you see the braces around the entire formula? If you cannot figure it out, edit your question again to provide the relevant data and show this formula as you have adapted it.
    – Ron Rosenfeld
    Commented Feb 6, 2018 at 11:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .