{} SumIf and CountIf

S

SS

I am trying to use the following formulas

=sumif(A2:A19,"3/0",M2:M19)/countif(A2:A19,"3/0")

=SUMIF(A2:A19,0,M2:M19)/COUNTIF(A2:A19,0)

columns A and M look as follows:
Size Post/Laser
0 1.112
0 1.099
0 1.105
0 1.111
0 1.112
0 1.116
0 1.116
3/0 1.109
3/0 1.105
3/0 1.097
3/0 1.094
3/0 1.106
3/0 1.112
3/0 1.117
3/0 1.094
3/0 1.094
3/0 1.107
3/0 1.090


In one case, the formula works beautifully. in the other case, the cell
spits back the formula. It does not give me the result. I seem to remember
having this problem before and pressing some combination of keys to get "{}"
to appear on either end of the formula and it working then, but I do not
remember the key combo.

Can anyone help? Why does this happen?

Thanks

SS
 
J

JE McGimpsey

You have the cell formatted as Text, so XL doesn't parse the entry as a
formula. Format the cell as General and reenter the formula.
 
P

Peo Sjoblom

Most likely you have formatted the cell with the formula as text or a leading
space or something, select the cell, press F2 and enter. Array entering (to
get curly braces) has anything to do with this, you can also try to replace =
with = to trigger caculation, your formula looks OK

Regards,

Peo Sjoblom
 
S

SS

Brain Freeze on my part. Thanks!

JE McGimpsey said:
You have the cell formatted as Text, so XL doesn't parse the entry as a
formula. Format the cell as General and reenter the formula.
Click to expand...
 
B

bj

Check what your second equation is formated as. If it is coming back as the
equation. the cell may be formated as text. if so reformat it as general


A second thing to try is to
try in your second equation
=SUMIF(A2:A19,0,M2:M19)/COUNTIF(A2:A19,"0")
If your 0 is a text zero rather than o number you would get an error.
 
Top