{} SumIf and CountIf

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

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.



In article <[email protected]>,
"SS" <[email protected]> wrote:

> 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?

the key combo is ctrl+shift+enter. Try this
=average(if(a2:a19="3/0",m2:m19))


--
Don Guillett
SalesAid Software
[email protected]
"SS" <[email protected]> wrote in message
news:[email protected]...
> 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

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

"SS" wrote:

> 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

Brain Freeze on my part. Thanks!

"JE McGimpsey" wrote:

> 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.
>
>
>
> In article <[email protected]>,
> "SS" <[email protected]> wrote:
>
> > 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?
>

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.

"SS" wrote:

> 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