Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count rows with specific attributes in VBA
Hi all,
I need to create a function that lets me pass in a range and a value that will iterate through all the rows in the range, check a specific column in each row for a specific value, and if it exists, add 1 to a counter. I want to do this in VBA, NOT using "SUM(IF" or "COUNTIF(" etc because these have limitations that don't work for me. So something like this: Foo(A1:A200, "matchVal") Where A1:A200 is the range (really the range of ROWS) to check and "matchVal" is the value to match on in whatever the column is that I specify within the code of the Foo function. Basically, I do not know how to, while iterating through cells in a Range, access a particular column and check it for a specific value. Can someone help? Thanks, Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count rows with specific attributes in VBA
Explain the limitations that SUMIF has, it is a better solution.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Steve Newhouse" wrote in message oups.com... Hi all, I need to create a function that lets me pass in a range and a value that will iterate through all the rows in the range, check a specific column in each row for a specific value, and if it exists, add 1 to a counter. I want to do this in VBA, NOT using "SUM(IF" or "COUNTIF(" etc because these have limitations that don't work for me. So something like this: Foo(A1:A200, "matchVal") Where A1:A200 is the range (really the range of ROWS) to check and "matchVal" is the value to match on in whatever the column is that I specify within the code of the Foo function. Basically, I do not know how to, while iterating through cells in a Range, access a particular column and check it for a specific value. Can someone help? Thanks, Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count rows with specific attributes in VBA
The "Count Unique Text Elements" solution proposed by Microsoft....
"=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1 :A10,A1:A10,0))0,1))" .... has a problem. It counts ONLY items that occur no more than once. I want to count each item, but only the first occurrence. So if there are three "X" values, I still want to count 1 of them, not 0, and for that first one, fall through to the next conditional test in the formula (joined by +, *, etc as is the custome w/array formulas). The other problem is that the above code appears to return an array of the size of the range + 1, which is annoying and means that subsequent conditional tests have to use A1:A11 to prevent error... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count rows with specific attributes in VBA
How about
=SUMPRODUCT((A2:A200<"")/COUNTIF(A2:A200,A2:A200&"")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Steve Newhouse" wrote in message ups.com... The "Count Unique Text Elements" solution proposed by Microsoft.... "=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1 :A10,A1:A10,0))0,1))" ... has a problem. It counts ONLY items that occur no more than once. I want to count each item, but only the first occurrence. So if there are three "X" values, I still want to count 1 of them, not 0, and for that first one, fall through to the next conditional test in the formula (joined by +, *, etc as is the custome w/array formulas). The other problem is that the above code appears to return an array of the size of the range + 1, which is annoying and means that subsequent conditional tests have to use A1:A11 to prevent error... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
Count rows with specific date | Excel Discussion (Misc queries) | |||
Count number of times a specific number is displayed in a cell ran | Excel Worksheet Functions | |||
How do I move rows showing a specific field to other worksheets/. | Excel Discussion (Misc queries) | |||
How do I move rows showing a specific field to other worksheets/. | Excel Discussion (Misc queries) |