Featured Product

Excel Daily News

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

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 MarkThe UDF won't work on a range of cells, I onlymade 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 haveyour selected Type number eg for formulas use

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

(8 responses) I have a column with either a value of 1 or blank. The sheet has a header. Is there code that can sort by value in column "Z", find the last

Let's Connect

MrExcel.com debuted on November 21, 1998.

MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on this
web site are provided "as is" and we do not guarantee that they can be used in all
situations.
This site contains affiliate links. Any affiliate commissions that we
earn when you click a link to Amazon or other sites is reinvested in keeping MrExcel.com
running. You can earn a commission for sales leads that you send to us by joining our
affiliate program.
View our Privacy Policy, Cookies Policy, and Terms of Use.