Count of Records IF

[gmaxey]

I have a worksheet with a column "Folder" and a column "Name"

I need to return the count where the Length of the folder path (Folder) and length of the File name "Name" exceeds 256

I have tried =CountIf(Len([Folder])+Len([Name]),>256) and several variation around that theme but obviously on the wrong track.

I need this to be a value returned in a VBA procedure:

e.g., Msgbox =CountIf(Len([Folder])+Len([Name]),>256)

Hope someone can help. Thank you!!

[Aflatoon]

You can't use COUNTIF for that because it will only accept ranges, not arrays. You could use Sumproduct and Evaluate:


Msgbox Evaluate("SUMPRODUCT(--(LEN([Folder]&[Name])>256))")

[gmaxey]

Unfortunately that returns a type mismatch error.

Folder Name
D:\Test\Test This is short name
D:\Test\Test\SubTest This is a very long file ...... name
D:\Test This is an very very long .......................... file name
D:\Test This is a short name

need to return 2 in the example above

[p45cal]

You will need to refer to the table name, eg.:

MsgBox [SUM(--(LEN(Table1[Folder]&Table1[Name])>256))]
and the active workbook needs to be the appropriate workbook (active sheet doesn't matter because the table name is unique in a given workbook).

[gmaxey]

What table name? It is the most basic workbook. A single worksheet with two columns with the headings "Folder" and "Name"

Folder column contains a Folder path
Name column contains a file name

I need to return the count of records where the length of the folder and name exceeds 256. Of course I could do this by looping through each record but was looking for a better solution.

Thank you.

[Aflatoon]

If it's not a table, you cannot refer to a column by its name. The syntax you posted originally is table syntax, so I think we just assumed you had a table. If not, any reason you can't make it into one? If you can't you'll need to use cell references instead.

[gmaxey]

I have no idea how to make it a table. As the workbook attached shows, the the actual sheet is very simple. Just a sheet with two columns.
Thank you.

[Aflatoon]

Select all the data then press Ctrl+T. By default it will be given the name Table1 and then the code in post #4 will work.

[gmaxey]

No joy. I opened the file attached in Post#5. I selected columns A and B and pressed Ctrl+t. The content of the sheet changed color theme from white to a dark blue header row then alternating light blue and grey rows. I assume this made the table.

I inserted the code from Post#4 and ran it. I get runtime error 13 Type mismatch.

Thanks

[p45cal]

I inserted the code from Post#4 and ran it. I get runtime error 13 Type mismatch.Attach the workbook (with the table and code) which does this.

[arnelgp]

can you use macro-enabled workbook (xlsm)?

Public Function fnCountHowManyLongLines(ByVal rng As Range) As Long
Dim arr As Variant
Dim i As Long, j As Long
Dim s As String
Dim cnt As Long
arr = rng
For i = 1 To UBound(arr, 1)
s = ""
For j = 1 To UBound(arr, 2)
s = s & arr(i, j) & ""
Next
cnt = cnt + Abs(Len(s) > 256)
Next
fnCountHowManyLongLines = cnt
End Function

based on your demo xlsm, put the formula on any cell:


=fnCountHowManyLongLines(A2:B3)

result: 1

[arnelgp]

or simplify:

Public Function fnCountHowManyLongLines(ByVal rng As Range) As Long
Dim arr As Variant
Dim i As Long
Dim cnt As Long
arr = rng
For i = 1 To UBound(arr, 1)
cnt = cnt + Abs(Len(arr(i, 1) & arr(i, 2) & "") > 256)
Next
fnCountHowManyLongLines = cnt
End Function

[gmaxey]

The file is attached. Thank you.

31810

[gmaxey]

arnelgp,

Thank you for this reply. I have already done something similar to that. The worksheet contains a list of files with attributes. Sometimes that list may be in excess of 30,000 items. I was looking for a way to get the count of long file paths without having to loop.

In the project, another piece of information needed is the "Size" of all files that are "Archived" I got that information using:


dblArchivedSize = WorksheetFunction.SumIf(oFileList.ListColumns("Archived").Range, "TRUE", oFileList.ListColumns("Size").Range)
varFileData = oSheet.UsedRange
For lngIndex = 2 To UBound(varFileData)
If Len(varFileData(lngIndex, 1) & varFileData(lngIndex, 2)) >= 260 Then
lngCount = lngCount + 1
End If
Next lngIndex

Again was looking for a one line function using one of the many Excel features that I remain ignorant of. Regardless, thanks again for your post and answer. If a one liner is not forthcoming I will definitely employ it.

[Aflatoon]

Your table is called Table4 not Table1, so you need to amend that in the formula in the code.

[p45cal]

If a one liner is not forthcomingYou have your one-liner in msg#4, here it is adjusted for your table's name:

MsgBox [SUM(--(LEN(Table4[Folder]&Table4[Name])>256))]
You may notice a distinct pause as this line produces its message. This is because your table extends to 1 million+ rows and it doesn't need to.
You can resize it to just as big as it needs to be (3 rows including the header). When you add more data directly below such a table it will automatically expand (and if it's not exactly right you can drag the table's grab handle in the bottom right corner and drag it to incude/exclude as much information as you want).

[gmaxey]

p45cal,

Yes, that is working nicely. Thank you (all of you) for your persistent help on this matter. I'm not bad with Word and Word VBA but seriously lacking with Excel.

[Aflatoon]

I'm not bad with Word and Word VBA but seriously lacking with Excel.

No problem - I am the opposite ;)

[gmaxey]

All

I posted a follow up on the method arnelgp provided: http://www.vbaexpress.com/forum/showthread.php?71913-How-to-pass-used-range-to-a-function-in-a-cell
Just out of curiosity, I would like to know if (and how) that might be used when the range is variable.