COUNTIF

[GDawg]

I have a column that contains values that have been directly keyed in and values that come from cells in other work sheets. Is there a COUNTIF (or other) formula that will count the number of cells that contain values not from other work sheets?

Thanks,

GDawg

[Jay Petrulis]

Hi Gdawg,

Not sure what you mean by this, but if you are talking about formulas in your data range vs. keyed in values, you would use VBA to do that.

If you are talking about comparing the data range with values on another worksheet, where you only want the number of cells that are *not* in other ranges, try soemthing like the following:

=COUNT(A1:A6)-SUMPRODUCT(COUNTIF(A1:A6,Sheet3!A1:A6))

This only searches the range on sheet3. If youneed multiple sheets, add addition SUMPRODUCT statements.

HTH,
Jay

[Aladin Akyurek]

Not sure what you mean by this, but if you are talking about formulas in your data range vs. keyed in values, you would use VBA to do that.

Exactly. Something like:

=COUNT(A1:A10)-COUNT(HasFormula(A1:A10))

where HasFormula returns an array constant of 1 and 0's.

[GDawg]

Jay and Aladin,

Thanks for responding. I guess I wasn't very clear. What I meant was, in the column are values that have been keyed in i.e., 12345.66 and a formula bringing data from a separate worksheet, i.e., =Sheet1!N29. I want to be able to count either the cells that contain a formula or the keyed data cells.

I'm not yet up to VB so are there any other options?

Thanks,

GDawg

[Jay Petrulis]

Hi,

How large is your range? You could also choose
Edit>Goto>Special and select Formulas or Constants to highlight the cells

Following Aladin's suggestion, the following UDF will count the cells containing a formula in your selected range.

Function HAS_FORMULA_COUNT(DataRange As Range)
Dim UsedCell As Range, Counter As Long

For Each UsedCell In DataRange
    If UsedCell.HASFORMULA Then Counter = Counter + 1
Next UsedCell
HAS_FORMULA_COUNT = Counter

End Function

This is a Visual Basic function created for this purpose. To load it, do the following:

1. Copy the code
2. Go to Excel and type the Alt and F11 keys simultaneously (Alt-F11)
3. Type Ctrl-R
4. From the top menu Insert>Module
5. Paste the code (Ctrl-V)
6. Alt-Q to return to Excel.

You have loaded the function to your file and can call it like you would a regular Excel function.

=HAS_FORMULA_COUNT(A1:A10)

and this will return the number of cells in range A1:A10 that are formulas. Subtract this number from the total number of cells in the range to determine the constants.

HTH,
Jay

EDIT: This might not update as you would like, so add:

Application.Volatile

right after the DIM statement lines.

EDIT2: Ignore this suggestion, as I had the calculation set to manual.

[ This Message was edited by: Jay Petrulis on 2002-05-15 13:26 ]

[ This Message was edited by: Jay Petrulis on 2002-05-15 13:35 ]

[GDawg]

Thanks Jay but....

I get a message Compile Error:

Expected line number or label or statement or end of statement

Where did I go wrong?

Thanks

GDawg

[Juan Pablo González]

Try this formula:

Function CountFormula(Rng As Range) As Long
CountFormula = Rng.SpecialCells(xlCellTypeFormulas).Count
End Function

[Jay Petrulis]

On 2002-05-15 13:31, Juan Pablo G. wrote:
Try this formula:

Function CountFormula(Rng As Range) As Long
CountFormula = Rng.SpecialCells(xlCellTypeFormulas).Count
End Function

Hi Juan,

I did something similar initially and I could not get it to work. Your function returns the total number of cells in the range, but I don't understand why. To me, it *should* work.

I messed up on Intersect(Rng, ...formulas), too.

To GDAWG: I cannot replicate your error. Please make sure that you copied the "End Function" into your code. It *should* work for you, too.

Bye,
Jay

[Aladin Akyurek]

On 2002-05-15 13:31, Juan Pablo G. wrote:
Try this formula:

Function CountFormula(Rng As Range) As Long
CountFormula = Rng.SpecialCells(xlCellTypeFormulas).Count
End Function

Why not one that returns an array of 0's and 1's, when fed with a range? I could use such a function.

[Jay Petrulis]

On 2002-05-15 13:42, Aladin Akyurek wrote:

Why not one that returns an array of 0's and 1's, when fed with a range? I could use such a function.

Hi Aladin,

Here is one that I got to work (at least for a 1-column range tested)

Function HAS_FORMULA_COUNT(DataRange As Range)
Dim UsedCell As Range, x, y
Dim MyArray

ReDim MyArray(1 To DataRange.Rows.Count, 1 To DataRange.Columns.Count)
    
With DataRange
For y = 1 To DataRange.Columns.Count
    For x = 1 To DataRange.Rows.Count
        If Cells(x + DataRange.Row - 1, y + DataRange.Column - 1).HASFORMULA Then
            MyArray(x, y) = 1
        Else
            MyArray(x, y) = 0
        End If
    Next x
Next y
End With
HAS_FORMULA_COUNT = MyArray

End Function

This requires that an equal sized worksheet range be selected and array-entered for this to work. (It has to be a worksheet array)

Let me know what needs to be done to improve/enhance/correct this.

Bye,
Jay