Counting based on a formula's result

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I had a question about counting based on the result of the formula
"Type(A1)". I needed to count cells in a range if their Type was =2. I also
wanted to accomplish this in a single formula.

In the past I have used Sumproduct to count. So to count the occurances of
the word "Car" in the range A1:A100-->

=Sumproduct((A1:A100="Car")*1)

I tried using sumproduct and the type function but it did not work.
Is there any way to do this, maybe with an array formula?

Thanks
 
Hi Jeff

the following works for me:
=COUNTIF(A1:A100,1)
and
=SUMPRODUCT((A1:A100=1)*1)

Cheers
JulieD
 
Julie,

I'm not trying to count 1 or 2 but the result of the "Type" of the cell,
either a formula, a number. Using the Type() function.

So I'm just trying to count the cells that have Type("cell") =2 for
formulas, or Type("cell") = 1 for numbers.


So range A1:A2 has formulas and numbers and I just want to count the number
of formulas or numbers. Using one formula.
 
Hi Jeff

sorry i misunderstood, i thought you were returning the type to another
column ... not sure how to achieve what you're after.

Cheers
JulieD
 
This array formula will count the number of text entries
in a range.
{=SUM(IF(ISTEXT(G1:G3),1,0))}
To count numbers, reverse the 1 and 0 or use not.
 
I'm confused. You say you want to count the number of "formulas or numbers",
and you refer to the TYPE function. Do you want to count the number of cells
that have a formula, as opposed to those that contain a constant? Is so, TYPE
won't fill the bill. Refer to the following from Help for TYPE:

"You cannot use TYPE to determine whether a cell contains a formula. TYPE only
determines the type of the resulting, or displayed, value. If value is a cell
reference to a cell that contains a formula, TYPE returns the type of the
formula's resulting value."
 
Here are two VBA functions to calculate the number of formulas and constants,
respectively. There were supposed to be "one-liners", e.g.

N = aRange.SpecialCells(xlCellTypeFormulas).Cells.Count

but things don't always turn out as expected <g>. See comments in the code.

Example worksheet formulas are

=COUNTFORMULAS(A1:B88)
=COUNTCONSTANTS(K23:O138)


Option Explicit

Function CountFormulas(aRange As Range) As Long
Dim A As Range
Dim N As Long
Dim Rng As Range

CountFormulas = 0

If aRange.Cells.Count = 1 Then
'SpecialCells will expand one cell to entire
'used range, so have to check manually
If aRange.HasFormula Then CountFormulas = 1
Exit Function
End If

On Error Resume Next
'next statement generates an error if there are no formulas
Set Rng = aRange.SpecialCells(xlCellTypeFormulas)
If Err.Number <> 0 Then Exit Function
On Error GoTo 0

'if Rng is a multiple selection, Rng.Cells.Count
'returns the number of cells in aRange, not Rng!
N = 0
For Each A In Rng.Areas
N = N + A.Cells.Count
Next A
CountFormulas = N

End Function

Function CountConstants(aRange As Range) As Long
Dim A As Range
Dim N As Long
Dim Rng As Range

CountConstants = 0

If aRange.Cells.Count = 1 Then
'SpecialCells will expand one cell to entire
'used range, so have to check manually
'if not empty and no formula, must be a constant
If IsEmpty(aRange.Value) = False Then
If aRange.HasFormula = False Then CountConstants = 1
End If
Exit Function
End If

On Error Resume Next
'next statement generates an error if there are no constants
Set Rng = aRange.SpecialCells(xlCellTypeConstants)
If Err.Number <> 0 Then Exit Function
On Error GoTo 0

'if Rng is a multiple selection, Rng.Cells.Count
'returns the number of cells in aRange, not Rng!
N = 0
For Each A In Rng.Areas
N = N + A.Cells.Count
Next A
CountConstants = N

End Function
 
Back
Top