Sum visible rows with text and numbers
 

I've been bashing my head against the wall trying to figure this one
out.

2 questions.

1) I want to sum an autofiltered row. This i can do. The problem lies
in that the filtered results contain both text and numerical values. I
want to sum only the numbers. When i try to do that it gives me
#VALUE!. Help.

2) I have a row of autofiltered information where i want to count the
rows. Again, this i can do. This time there are duplicate values, which
i don't want to count. I tried using a function combined with
FREQUENCY/MATCH, but at this point i've been starring at it too long.

Save my brain.

Thanks


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Sum visible rows with text and numbers
 

Jmbo,

#1:
Have you tried Subtotal(9, "A1:A30") in a formula,
or Application.Worksheetfunction.subtotal(9,
Sheets("Sheet1").Range("A1:Z1")) in VBA?
This works find for me with autofilters and text values


#2:
I would (in VBA) use a collection to count the unique values in the row

Sub TestxC()
Dim xcColCount As New Collection
Dim cel As Range
i = 0
On Error Resume Next
For Each cel In Sheets("Sheet1").Range("A1:Z1").Cells
i = i + 1
If Not cel.EntireColumn.Hidden Then xcColCount.Add i,
CStr(cel.Value)
Next
MsgBox xcColCount.Count

End Sub

The collection will not permit duplicate keys (the second term in the Add
Method), and the error will be skipped based on the ON ERROR statement. This
will ignore filtered values (using entirecolumn.hidden)

If you want to ignore text in this process you will need to get a little
more fancy.

Is this helpful?
Alex J

"Jmbostock" wrote in message
...

I've been bashing my head against the wall trying to figure this one
out.

2 questions.

1) I want to sum an autofiltered row. This i can do. The problem lies
in that the filtered results contain both text and numerical values. I
want to sum only the numbers. When i try to do that it gives me
#VALUE!. Help.

2) I have a row of autofiltered information where i want to count the
rows. Again, this i can do. This time there are duplicate values, which
i don't want to count. I tried using a function combined with
FREQUENCY/MATCH, but at this point i've been starring at it too long.

Save my brain.

Thanks


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/