Extract Records From Different Ranges and Filter Results

  • Hello fellow members,

    Today I have 3 unique problems with my worksheet project. What I am trying to do is to obtain an accurate count and listing of UI codes by the two digit range (e.g. 00-09, 10-19, 20-29, etc.) whenever I sort by division so that I can assign a workload to my staff. The original worksheet contains over 5000 lines of data with various contract numbers, UI codes, dates and divisions.

    Problem #1: =COUNTIFS($G$4:$G$13,">="&I17,$G$4:$G$13,"<="&I18))

    The count is not accurately working for the formula in the yellow highlighted row 16 cells I16-Q16.
    The results should be I16=1; K16=2; L16=1; M16=2; N16=1 and P16=1, J16 & O16 should =0, but since the formula isn’t working properly it defaults to zero. I populated the same results from cells G4:G13 in cells H3:H13 to illustrate that the formula does work for cell H16 highlighted by green, but I really need it to accurately count from the data in cells G4:G13 for the cell in rows I16:Q16.

    Problem #2: =IF(ROWS(H$23:H25)>$H$16,"",INDEX($G$4:$G$13,SMALL(IF($G$4:$G$13>=H$17,IF($G$4:$G$13<=H$18,ROW($G$4:$G$13)-ROW($G$4)+1)),ROWS(H$23:H25))))

    The correct formula is in rows H23:H25, which references the count in row H16 in order to prevent the further showings of the “#NUM!” results illustrating the UI code results are only 02, then 01 which is accurate. However, if I was to copy this formula to the remaining cells in rows I23:Q25 with the correct formula in cells I16:Q16 it would provide blank results in cells I23:Q25 which would be correct if the counts in I16:Q16 were actually zero, but this is not correct. I intentionally left cells I24, I25 and J23:Q25 formulas incomplete to illustrate what the correct results should show.

    Problem #3: No formula for this issue, I was thinking of inserting “Subtotal” instead of “Small”, but that doesn’t seem to work.

    If I was to sort by division “North” the correct count in cell H16 should equate to zero, but it remains a count of 2 and cell M16 should equate to 2. Since the count isn’t working properly on the division sort all the other cell counts from I16:Q16 would be incorrect which would also render in correct results for UI-codes in cells H23:Q25.

    Attached is the sample worksheet.

    I appreciate your review and resolve on this complex formula.

  • Re: Extract Records From Different Ranges and Filter Results

    Hi Kris,

    Thank you for your recommendations. I'm not totally sure what the "=--(E4&F4)" actually does, but it seems to work. I have made the corrections to my workbook based on your findings and recommendations. However, this recommendation only solves problems #1 and #2. I'm still having an issue with the sort, count and correct extraction of records as listed in my original post for problem #3. Any suggestions?

    Thanks in advance

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!