I could use some help with creating an Excel 97, SR-2, cell formula.

I am trying to look at a column of numbers with entries of "1" thur "5" and
text that indicates "N/A" that are grouped in B8:B11, B13:B19, B21:B31 and
B33:B34.

If the entry in any of these cells is not a number "1" thru "5" but has
"N/A", then I would like to subtract 5 from 120 for every N/A found in this
above non sequential range of cells.

Try

=120-(5*(COUNTIF(B8:B11,"N/A")+COUNTIF(B13:B19,"N/A")+
COUNTIF(B21:B31,"N/A")+COUNTIF(B33:B34,"N/A")))

If this post helps click Yes
---------------
Jacob Skaria


"CAS" wrote:

I could use some help with creating an Excel 97, SR-2, cell formula.

I am trying to look at a column of numbers with entries of "1" thur "5" and
text that indicates "N/A" that are grouped in B8:B11, B13:B19, B21:B31 and
B33:B34.

If the entry in any of these cells is not a number "1" thru "5" but has
"N/A", then I would like to subtract 5 from 120 for every N/A found in this
above non sequential range of cells.

Try this:

=120-(5*(COUNTIF(B8:B34,"N/A")-(B12="N/A")-(B20="N/A")-(B32="N/A")))

If the cells between ranges *will never* contain N/A then:

=120-5*COUNTIF(B8:B34,"N/A")

--
Biff
Microsoft Excel MVP


"CAS" wrote in message
...
I could use some help with creating an Excel 97, SR-2, cell formula.

I am trying to look at a column of numbers with entries of "1" thur "5"
and
text that indicates "N/A" that are grouped in B8:B11, B13:B19, B21:B31 and
B33:B34.

If the entry in any of these cells is not a number "1" thru "5" but has
"N/A", then I would like to subtract 5 from 120 for every N/A found in
this
above non sequential range of cells.

=120-5*(SUM(COUNTIF(INDIRECT({"B8:B11","B13:B19","B21:B 31","B33:B34"}),"N/A")))


"CAS" wrote:

I could use some help with creating an Excel 97, SR-2, cell formula.

I am trying to look at a column of numbers with entries of "1" thur "5" and
text that indicates "N/A" that are grouped in B8:B11, B13:B19, B21:B31 and
B33:B34.

If the entry in any of these cells is not a number "1" thru "5" but has
"N/A", then I would like to subtract 5 from 120 for every N/A found in this
above non sequential range of cells.