Counting Query - Excel 2003

[KFlaps]

Hi all -i'm brand new to this board (though I think i'll be on here quite a bit! )

I have a problem in excel that i just can't get my head around, i'll try to pose the question as simply as possible as trying to explain why i need this is a bit of a headache!!!

Basically, i have a list of data, all in one column, approx 1000 rows deep. About half of this data is over 3 characters long, and the rest is less than 3 characters...

What i need to do is count all the cells in this column that contain text over 3 characters long. I need this as an automatic formula as the number of cells with data over 3 characters can change on a daily basis...

I've tried using combinations of IF, LEN, COUNTA and COUNTIF, but i either get "0" or "994" (the total number of cells with any characters in).

For ease of explanation of any replies, this list of data is Named: NameList1.

So for example, i've tried:

=IF(LEN(Z2)<=3,"Yes","No")

this works fine if i drag the forumla down next to my column of data as it identifies everything over 3 characters with a "no" and anything under 3 characters with a "yes"

So i tried:

=IF(LEN(Z2)<=3,"Yes",COUNTA(NameList1))

but this obviously doesn't work as it's only refering to the first cell in my list of data - and it gives me a result of 994...if i change the "Z2" to "NameList1" i get a result of "0" (the "Yes" isn't important, it's just to make it easier for me to follow the formula)

I've also tried:

=COUNTIF(NameList1,LEN(NameList1)>3)

but I still just get "0"

I'm fairly competant at excel but i'm no genius, most of what i know i've figured out by browsing forums and figuring out other peoples formulas....so please treat me like a layman

Pleeeease Help!!! Thankyou!!!!

Edit: I'm running Excel 2003

 

[evnoort]

Hi KFLAPS
Put this code onder a button on your sheet. Result in cellB1
Regards.

Private Sub CommandButton1_Click()
Dim i As Integer
Dim Result As Integer

Result = 0

For i = 1 To 1000
If Len(Range("a" + CStr(i))) > 3 Then
    Result = Result + 1
End If
Next

Cells(1, 2) = Result

End Sub

 

[AlphaFrog]

Try something like this...

=SUMPRODUCT(--(LEN(NameList1)>3))

 

[KFlaps]

Hi AlphaFrog - that works great, thankyou! I've never used the SUMPRODUCT formula before so will have to commit to memory - what is this formula normally used for? And can is ask - i've never seen the double dash (--) used before...how and why would i use that?

Soz to ask questions, feel free not to reply, but if i learn now i won't have to ask again (in theory)

Thanks evnoort for the reply too!

 

[KFlaps]

Woo Hoo! I've managed to nest your formula in an OFFSET formula so that now i have a dynamic drop-down list that only shows data > 3 characters, and grows when any new data > 3 characters is added!!!

=OFFSET(NameList1,0,0,SUMPRODUCT(--(LEN(NameList1)>3)),1)

This has been giving me a headache for 2 days now! God Bless these forums

 

[AlphaFrog]

The SUMPRODUCT function is used for a variety of needs. Most often as a multiple criteria COUNTIF or SUMIF type function, but there ore other uses.

I think this video demonstrates it's versatility better than I ever could.
Video: SUMPRODUCT function 12 Examples

 

[KFlaps]

The SUMPRODUCT function is used for a variety of needs. Most often as a multiple criteria COUNTIF or SUMIF type function, but there ore other uses.

I think this video demonstrates it's versatility better than I ever could.
Video: SUMPRODUCT function 12 Examples

Awesome, thanks AlphaFrog!