Consulting

Results 1 to 19 of 19

Thread: Count of Records IF

  1. #1
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location

    Count of Records IF

    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!!
    Greg

    Visit my website: http://gregmaxey.com

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,779
    Location
    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))")
    Be as you wish to seem

  3. #3
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location
    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
    Greg

    Visit my website: http://gregmaxey.com

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    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).
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location
    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.
    Attached Files Attached Files
    Greg

    Visit my website: http://gregmaxey.com

  6. #6
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,779
    Location
    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.
    Be as you wish to seem

  7. #7
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location
    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.
    Greg

    Visit my website: http://gregmaxey.com

  8. #8
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,779
    Location
    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.
    Be as you wish to seem

  9. #9
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location
    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
    Greg

    Visit my website: http://gregmaxey.com

  10. #10
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    Quote Originally Posted by gmaxey View Post
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  11. #11
    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

  12. #12
    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

  13. #13
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location
    The file is attached. Thank you.

    Book1.xlsm
    Greg

    Visit my website: http://gregmaxey.com

  14. #14
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location
    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.
    Last edited by Aussiebear; 10-11-2024 at 07:46 PM. Reason: Added code tags to supplied code
    Greg

    Visit my website: http://gregmaxey.com

  15. #15
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,779
    Location
    Your table is called Table4 not Table1, so you need to amend that in the formula in the code.
    Be as you wish to seem

  16. #16
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    Quote Originally Posted by gmaxey View Post
    If a one liner is not forthcoming
    You 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).
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  17. #17
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location
    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.
    Greg

    Visit my website: http://gregmaxey.com

  18. #18
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,779
    Location
    Quote Originally Posted by gmaxey View Post
    I'm not bad with Word and Word VBA but seriously lacking with Excel.
    No problem - I am the opposite
    Be as you wish to seem

  19. #19
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location
    All

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

    Visit my website: http://gregmaxey.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •