Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 22nd 06, 02:43 PM posted to microsoft.public.excel.worksheet.functions
Steve Newhouse
 
Posts: n/a
Default 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   Report Post  
Old February 22nd 06, 02:56 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Old February 24th 06, 10:27 PM posted to microsoft.public.excel.worksheet.functions
Steve Newhouse
 
Posts: n/a
Default 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   Report Post  
Old February 25th 06, 02:30 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert rows based on specific value bob Excel Worksheet Functions 6 February 29th 08 08:11 PM
Count rows with specific date lakegoddess Excel Discussion (Misc queries) 3 August 9th 05 04:36 PM
Count number of times a specific number is displayed in a cell ran subs Excel Worksheet Functions 1 June 27th 05 05:01 PM
How do I move rows showing a specific field to other worksheets/. jmp Excel Discussion (Misc queries) 1 March 4th 05 06:42 AM
How do I move rows showing a specific field to other worksheets/. jmp Excel Discussion (Misc queries) 0 March 4th 05 05:27 AM


All times are GMT +1. The time now is 02:13 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017