Countif (A1:A5) has formula using the good TypeF function suggested by Ivan Moala

Posted by Mark P. on June 05, 2000 6:59 AM

Ivan now that you helped me out with Countif Isnumber (thank you very much) I have the last question. In my project actualy I need to count if cells are numbers ONLY, and to exclude cells that have formulas in them. To find out if they have formula, I tried to use your User defined function posted by you last year,called TypeF, which is excelent BTW (I used it many times successfully). For the range A1:A5, A1=3, A2=4, A3=B1+C7, A4=BLANK, A5=7. I tried to count cells with formulas using this formula: =countif(A1:A5,TypeF(A1:A5)=8) but I get zero, or when I use
=IF(OR(typef(A1:A3))=8,COUNT(A1:A3),)also does not work.Thank you again for help.

Posted by Ivan Moala on June 05, 0100 7:37 PM

Hi Mark
The UDF won't work on a range of cells, I only
made it for a single cell entry....should change it.....

You could try this instead, if it suits ??

This will count all cells in a range that have
your selected Type number eg for formulas use

CountTypeRg(A1:A5,8) for numbers use
CountTypeRg(A1:A5,1) etc.

HTH
Ivan

Function CountTypeRg(MyRange As Range, TypeN As Integer) As Integer
Application.Volatile
Dim oCell
'/==========================\
'/ Counts Rg of cells that \
'/ have the following Types \
'/======TypeN Values========\
'/ Number = 1 \
'/ Text = 2 \
'/ Logical value = 4 \
'/ Formula = 8 \
'/ Error value = 16 \
'/ Array = 64 \
'/==========================\

For Each oCell In MyRange.Cells
If oCell.HasFormula And Not (WorksheetFunction.IsError(oCell.Value)) Then
If TypeN = 8 Then CountTypeRg = CountTypeRg + 1
ElseIf oCell.HasArray Then
If TypeN = 64 Then CountTypeRg = CountTypeRg + 1
ElseIf WorksheetFunction.IsNumber(oCell.Value) Then
If Type