program formula to ignore any cell displaying "N/A"
 

I have built a complicated workbook that pulls from
individual worksheets. I am searching for a way to build
a formula that will calculate a range of cells. That
range consists of some that are used and some that are
not. The ones that are not used are displaying "N/A" as a
result of the formula build.
Can anyone tell me how to build a formula and or function
formula that can calculate the range using only the cells
with a value in them. That range will remain constant but
the cells will change from "N/A" to a value each time it
is used.
Thanks for your help
Ed P

program formula to ignore any cell displaying "N/A"
 

Ed P,
My Test range is P2:P12
to sum
=SUMIF(P2:P12,"<#N/A")
to count
=COUNTIF(P2:P12,"<#N/A")
and array formulas (hit Enter while keeping Ctrl and Shift keys down)
=AVERAGE(IF(ISERROR(P2:P12),"",P2:P12))
=MAX(IF(ISERROR(P2:P11),"",P2:P11))
HTH
Cecil

"Ed P" wrote in message
...
I have built a complicated workbook that pulls from
individual worksheets. I am searching for a way to build
a formula that will calculate a range of cells. That
range consists of some that are used and some that are
not. The ones that are not used are displaying "N/A" as a
result of the formula build.
Can anyone tell me how to build a formula and or function
formula that can calculate the range using only the cells
with a value in them. That range will remain constant but
the cells will change from "N/A" to a value each time it
is used.
Thanks for your help
Ed P