Dynamic Range Sum Based On Single, Dynamic Criterion Cell

[mbeane]

Column B = Employee initials. Column H = Parts run per jobs in rows 2 through 8. Data from another 60+ employees follows this representative batch. Employee initials will always be in column 2 and will always be in the row above the first line entry for job numbers and parts manufactured. There will always be a blank cell in column H after the last part count entry. The number of row entries will always vary from employee to employee; from one or two to over a hundred. From this data and scenario, I need to sum the number of parts run by the employee, based on the employee's initials.


Employee: ABG Gold, Alfred B. Type: Shop Parts

18714C CNCMILL5TH 0040 0.00 29

19096 CNCMILL5TH 0020 0.00 10

19105 CNC MILL 0040 0.00 62

19286C CNCMILL5TH 0030 0.00 21

19286C CNCMILL5TH 0040 0.00 34

19289B CNC MILL 0050 0.00 13

19289B CNC MILL 0060 2.00 2.00 6.00 33% 48


In old Lotus 123 terms, I could find "ABG", move right 6 (to column H), then highlight and sum H2 through H8 (until I find the blank cell at the end of ABG's parts column). I do not know VBA. There are, currently, over 32,000 rows of data that I need to sum by employee. In the example, above, my synopsis sheet would report that "ABG" ran 188 parts.


HELP!!!


Respectfully submitted, MB

 

[kchiba]

Hi Mbeane,


I am not sure what is in your columns or how the columns are split, can you maybe give a representation with two employees

 

[Hui]

mbeane

If I read your question correctly, you want a formula to go in H1 and subsequent employees rows which will sum down until it finds a blank in the same column (H)

=SUM(OFFSET(H1,1,0,MATCH(TRUE,INDEX(ISBLANK(H2:$H$100000),0,0),0)-1,1))

and copy to the other Employee Rows