A | B | C | D | E | F | G | H | I | J | |

1 | Data | IsNumber | IsFormula | |||||||

2 | 5 | HH | 7 | 3 | 4 | 8 | T | 5 | #VALUE! |

<tbody>

</tbody>

For each row I need to know how many of the entries in columns B-H are numbers. I use this array formula in I2 and works perfect: ={SUM(IF(IsNumber(B2:H2),1,0))} (Ctrl+Shift+Enter), the result is 5.

For all the data in columns B-H there is a formula, but many times the values have to be entered manually for special reasons. I need to identify those and for that purpose I created this udf:

Function IsFormula(r As Range) As Boolean

IsFormula = r.HasFormula

End Function

which works when used for a single cell, but when trying to apply to a vector it fails. If I use the formula ={SUM(IF(IsFormula(B2:H2),1,0))} (Ctrl+Shift+Enter) in J2 it returns a #VALUE! Error, when the answer should be 4. I highlighted the cells with formula for the purpose of this example.

I've done some research and it seems my problem is that the udf is only prepared for a single cell, but I haven't figured out how to correctly declare and/or dimension the function to be able to work fine with arrays. Hope you can help.