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

## Files

• Re: Extract Records From Different Ranges and Filter Results

Hi

In G4 and copied down,

=--(E4&F4)

H17:Q18 convert these values into numbers

H16 and copied across

=COUNTIFS(\$G\$4:\$G\$13,">="&H17,\$G\$4:\$G\$13,"<="&H18)

• 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?