Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding a number within a text and numbers for multiple rows | Excel Worksheet Functions | |||
Conditional Formatting of numbers, but not text, in recurring rows | Excel Worksheet Functions | |||
Deleting Visible Rows | Excel Discussion (Misc queries) | |||
Using COUNTIF with visible rows only | Excel Worksheet Functions | |||
Counting rows containing data (both numbers and text) | Excel Worksheet Functions |