como te dice jchuk, la función contara() cuenta la cantidad de celdas no vacías, esto es independiente de si en la celda a contar hay un numero, texto, una formula, una formula que devuelve error, un espacio en blanco, lo que devuelve la función es un numero y como tal puede ser sumado.
si tengo celdas con formato texto y luego quiero poner una formula (=contara...) en esa celda no se puede porque lo tomara como texto ('=contara...), ahora si tengo una celda con formato general y pongo una formula esta si devolverá un resultado, si luego cambio el formato de celda a texto
(no deberías cambiarlo), mientras no modifique la formula, se mantendrá el resultado y si este es un numero, así este con formato de texto, podrá ser sumado.
haciendo pruebas las únicas formas de que la suma me devuelva 0 fue poniendo las celdas con formato texto y posteriormente poner directamente los números (sin formulas), por ejemplo haciendo una copia solo valores sobre celdas en formato texto o sumando celdas vacías.
por ahí si sigues el consejo de jchuk de poner un ejemplo real o subes un archivo obviamente con valores falsos pero que te de error
