COUNTIF help!!

[Celt]

I am having trouble with the COUNTIF Function. I am trying to use a wildcard in my criteria.

My data is all contained in column E. It looks like this

40400
40400
40420
40430
40500
40600

This is the formula I am using =COUNTIF(Sheet1!$E:$E,"404*")
Baisclly I want the function to return the number of rows that have either 40400, 40420 or 40430 (ie. 4).

If I change out the "404*" with 40400, it works perfectly.
Am I missing something?

Should I be using operators instead of wildcards?

Any help is appreciated!!!

[anilsolipuram]

Resort to this if there is no other alternative.

I wrote a userdefined function,. which will take range,number as the paramter and return count of numbers of cells whose cell value is like the number( eg 404*)

to search 404* , in any cell you have to type =calc(E1:E20,"404") it will search for 404* in that range(E1:E20) and return the count.

You have to paste the function at tools->macro->visual basic editor, at visual basic editor go to the toolbar , insert->module , now past the code in the editor space.


Function calc(r As Range, find_number As String)
Dim count_if As Variant
Dim i As Variant
For Each c In r
If Left(c.Value, 3) = find_number Then
count_if = count_if + 1
End If
Next
calc = count_if
End Function

[BenjieLop]

This may not be elegant but it will work (as I tested it):

1. In cell F1, enter the formula =--left(E1,3) and copy down until cell F6
2. In cell G1, enter the formula =countif(F1:F6,"=404")

And based on your given data, it will return 4.

I am having trouble with the COUNTIF Function. I am trying to use a wildcard in my criteria.

My data is all contained in column E. It looks like this

40400
40400
40420
40430
40500
40600

This is the formula I am using =COUNTIF(Sheet1!$E:$E,"404*")
Baisclly I want the function to return the number of rows that have either 40400, 40420 or 40430 (ie. 4).

If I change out the "404*" with 40400, it works perfectly.
Am I missing something?

Should I be using operators instead of wildcards?

Any help is appreciated!!!

[Celt]

Thanks for the advice guys!

I'll give these a shot.

[jhalverson]

Why not try this?
=SUM(($E$1:$E$6<>"")*((LEFT($E$1:$E$6,3)="404")))
When you enter this, be sure to press ctrl-shift-enter to create an array function.
If you add data to your column, be sure to change the $E$6 value to the value of the last filled cell. Or you can even put $E$1000, since the first part of the function only counts filled cells, while the second part checks the first 3 characters for "404".

Jason

[Celt]

Thanks!

That worked great. I should ahve given more detail in my original request.

I am writing a macro (i'm a beginner) to manipulate a large quantity of raw financial data from my general ledger. I am using dynamic ranges in the macro that adjust themselves based on the data pulled. This is an example of the dynamic range formula I am using:

=OFFSET(INDIRECT(ADDRESS(MATCH(40400,Sheet1!$E:$E,0),5)),0,-3,COUNTIF(Sheet1!$E:$J,40400),COUNTA(Sheet1!$4:$4))

This works great if I only want to extend the range downward for as many rows that contain account 40400. However, I would like the range to extend down for all accounts beginning with 404 (as they roll up together). So when I drop in the last formula provided by Jason it works just fine...though it does make my computer "think" for a bit.... is there a better way???

=OFFSET(INDIRECT(ADDRESS(MATCH(40400,Sheet1!$E:$E,0),5)),0,-3,SUM((Sheet1!$E$4:$E$65536<>"")*((LEFT(Sheet1!$E$4:$E$65536,3)="404"))),COUNTA(Sheet1!$4:$4))

I have used 65536 as the amount of data will vary at all times. Is there a way to use some sort of open arguement (like $E:$E)?

[jhalverson]

I'm also looking for a function that will only calculate up to the last filled cell in a column. I'm assuming it may have something to do with the INDEX function, but can't figure it out. I've posted in the Function forum, but so far, to no avail.
If I get an answer, I'll post it here.

Jason

[Domenic]

Try the following...

First, define the following references...

Insert > Name > Define

Name: BigNum
Refers to: =9.99999999999999E+307

Name: AcctNum
Refers to:
=Sheet1!$E$4:INDEX(Sheet1!$E$4:$E$65536,MATCH(BigNum,Sheet1!$E$4:$E$65536))

Then, use the following...

=OFFSET(INDEX(AcctNum,MATCH(40400,AcctNum,0)),0,-3,SUM((AcctNum<>"")*(LEFT(AcctNum,3)="404")),COUNTA(Sheet1!$4:$4))

Hope this helps!

Thanks!

That worked great. I should ahve given more detail in my original request.

I am writing a macro (i'm a beginner) to manipulate a large quantity of raw financial data from my general ledger. I am using dynamic ranges in the macro that adjust themselves based on the data pulled. This is an example of the dynamic range formula I am using:

=OFFSET(INDIRECT(ADDRESS(MATCH(40400,Sheet1!$E:$E,0),5)),0,-3,COUNTIF(Sheet1!$E:$J,40400),COUNTA(Sheet1!$4:$4))

This works great if I only want to extend the range downward for as many rows that contain account 40400. However, I would like the range to extend down for all accounts beginning with 404 (as they roll up together). So when I drop in the last formula provided by Jason it works just fine...though it does make my computer "think" for a bit.... is there a better way???

=OFFSET(INDIRECT(ADDRESS(MATCH(40400,Sheet1!$E:$E,0),5)),0,-3,SUM((Sheet1!$E$4:$E$65536<>"")*((LEFT(Sheet1!$E$4:$E$65536,3)="404"))),COUNTA(Sheet1!$4:$4))

I have used 65536 as the amount of data will vary at all times. Is there a way to use some sort of open arguement (like $E:$E)?