i have a large list of raw data that looks something like this Code: Name Code hours Abbott CA1 13.5 Abbott CA2 80 Abbott CT2 11.25 Abbott CT3 40 Abbott OT2 1.125 Abbott OT3 4 Abbott SBY 36.34 Ashir OT2 103.5 Ashir OT3 156 Ashir SBY 7 Ashton CT2 105.75 Ashton CT3 46 Ashton OT2 -14.625 Ashton OT3 -4 I need to get the sums of each earning code into another worksheet. all cells where the the earning codes match ( ie all that start with OT, all that start with CA and so on) need to be added up and i need it to do this for each name for example Abbott would give me a total of 93.5 for CA, 51.25 for CT, 5.125 for OT and 36.34 for SBY. then it would move on to Ashir and so on. The tricky part is that not all employees have hours listed for each of the feilds. Hopefully ive given enough information for some help. Im comfortable enough with VBA so if its easier i can do a macro but if theres a function that can work id rather not code it myself. Thanks

i guess what i need is a function that returns a range of all cells with name and code matching and then i could nest it in the SUM function?

With stuff like that where I want to avoid using VBA I normally do the following: 1. Add a formula in the column next to the table that gives a value of 1, 2, 3, etc and drag it down: Code: =if(LEFT(B2,2)=CA,1,if(LEFT(B2,2)=OT,2,etc...) 2. Then I just do a sumif using the new column as the criteria.