Consulting

Results 1 to 11 of 11

Thread: Counts min and max quantity of equals 'strings' based on a filter column

  1. #1
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location

    Counts min and max quantity of equals 'strings' based on a filter column

    Good morning


    in Excel 2021


    I need to count the minimum and maximum quantity of duplicate strings in J9:J70 according to the value in column H


    So in the attached example a formula placed in K3 should return 6, in L3 11, in M3 2 and in N3 9


    ...I don't know how to modify the formula I put in K3...


    Thanks if anyone can help me
    Attached Files Attached Files

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    323
    Location
    For a start, figure out how to calc min and max values - How to Find Lowest Value with Criteria in Excel (7 Effective Ways) (exceldemy.com)

    Minimum must be number greater than 1?

    You want to use multiple criteria for counting so use COUNTIFS.

    In my first simple attempt, values in J cells are read as text and return 0. Converting values to numbers returns maximum and minimum.

    Unfortunately, MINIFS() does not seem to be available with Excel 2010 Excel MINIFS function | Exceljet, same for COUNTIFS.

    Explain the formula some more - why multiply by result of $H$9:$H$70=K1 ?


    Wait, you want the count of values that have the least and most rows, not count of the maximum and minimum values? That changes everything for me. Back to square 1.
    Last edited by June7; 05-27-2023 at 12:22 PM.
    How to attach file: How to upload your attachments (vbaexpress.com) To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Not sure if this is the best way to complete your task, but complete the following steps

    1. in cell(K9), create an advanced filter, using Copy to Range, List Range of $J$9:$J$70, Criteria range J9, Copy to K8, and select Unique records only. Click OK.

    2. This will show a completed range of K9:K18, now in cell L9 enter the formula "=Countif($J$9:$J$70,K9)", and fill down. This gives the count of occurrences.

    3. Since you require predetermined values in the following cells K3:N3, namely 6,11,2,9.
    in Cell K3 enter the formula "=Small(L9:L18,4)",
    in Cell L3 enter the formula "=Large(L9:L18,1)",
    in cell M3 enter the formula "=Small(L9:L18,2)", &
    in Cell N3 enter the formula "=LargeL9:L18,2)".
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    323
    Location
    Aussie, I tried suggestion.

    The unique list repeats the 000001 value.

    The SMALL() results are not correct.

    The LARGE results are correct but I don't think would always be the case. Because, as far as I can tell, these formulas do not take into account grouping defined by H column.

    Correction, I do have COUNTIFS available (still not MINIF, MINIFS, MAXIF, MAXIFS). Just not sure it can be helpful.

    This does work.

    On each row K9:K70, calculate: =COUNTIF($J$9:$J$70,J9)

    Then 4 array formulas:

    {=MIN(IF($H$9:$H$70=1,$K$9:$K$70))}
    {=MAX(IF($H$9:$H$70=1,$K$9:$K$70))}
    {=MIN(IF($H$9:$H$70=2,$K$9:$K$70))}
    {=MAX(IF($H$9:$H$70=2,$K$9:$K$70))}

    Simpler than I expected. You probably have MINIF and MAXIF available.
    Last edited by June7; 05-27-2023 at 09:14 PM.
    How to attach file: How to upload your attachments (vbaexpress.com) To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    Good morning june7 and Aussiebear


    thank you for your availability


    the last solution of june7 works perfectly and is the simplest to use for me, I just had to adjust the syntax for the separator characters and the name of the functions in Italian


    thanks again for your support

  6. #6
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Quote Originally Posted by June7 View Post
    Aussie, I tried suggestion.

    The unique list repeats the 000001 value.
    No it doesn't. Try again.

    The SMALL() results are not correct.
    Worked here on my sheet.

    The LARGE results are correct but I don't think would always be the case.
    They met the required predefined results as asked for.

    This does work.

    On each row K9:K70, calculate: =COUNTIF($J$9:$J$70,J9)
    Except the values in K9:K70 are often repeated hence the need to create a Unique listing.

    Then 4 array formulas:

    {=MIN(IF($H$9:$H$70=1,$K$9:$K$70))}
    {=MAX(IF($H$9:$H$70=1,$K$9:$K$70))}
    {=MIN(IF($H$9:$H$70=2,$K$9:$K$70))}
    {=MAX(IF($H$9:$H$70=2,$K$9:$K$70))}

    Simpler than I expected. You probably have MINIF and MAXIF available.
    As I tried to indicate, there are probably other methods that will work.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  7. #7
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    Sorry June7

    It is possible to change the formula {=MIN(IF($H$9:$H$70=1,$K$9:$K$70))} or use another one e.g. MIN.PLUS.IF so that the number 1 is excluded from the counts or the minimum value ?


    thanks as much as you can let me know

  8. #8
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    323
    Location
    I tried several times. Cannot get your calcs to work. First two rows in the distinct list are 000001. Change the data a bit and even the Large() calcs will not return correct values because not considering the grouping of column H.


    As far as I can tell, values do not duplicate between the H groups, however, could allow for that with: =COUNTIFS($H$9:$H$70,H9,$J$9:$J$70,J9)
    How to attach file: How to upload your attachments (vbaexpress.com) To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    ok thanks anyway


    I had searched various sites and thought that an instruction to exclude the evaluation of the value 1 could be included in the formula and I thought it was just a matter of using the correct syntax.


    thanks again for your support

  10. #10
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    323
    Location
    Possibly could with MINIFS and MAXIFS but I don't have those functions available in Excel 2010 to test.

    =MINIFS($K$9:$K$70, $H$9:$H$70, 1, $J$9:$J$70, "<>000001")

    Review https://professor-excel.com/how-to-u...mula-in-excel/
    How to attach file: How to upload your attachments (vbaexpress.com) To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    Hi June7


    in fact with the previous solution I had found some inconsistencies


    this is a great tip, i have this function in my excel 2021 and it does exactly what i wanted...i missed it in your first post


    Thanks again

Posting Permissions

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