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.