Excel help: sum of all cells where name is ___ and earning code is ____

Discussion in 'OT Technology' started by Scream_Phoenix, Sep 8, 2008.

  1. Scream_Phoenix

    Scream_Phoenix Handsome Boy Model

    Joined:
    May 31, 2001
    Messages:
    45,110
    Likes Received:
    156
    Location:
    Capital City
    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. :hs:

    Thanks
     
  2. Scream_Phoenix

    Scream_Phoenix Handsome Boy Model

    Joined:
    May 31, 2001
    Messages:
    45,110
    Likes Received:
    156
    Location:
    Capital City
    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?
     
  3. EkriirkE

    EkriirkE Zika Xenu OT Supporter

    Joined:
    Jan 11, 2004
    Messages:
    14,799
    Likes Received:
    0
    Location:
    Dublin & San Francisco, CA
    use filtering and SUMIF
     
  4. dissonance

    dissonance reset OT Supporter

    Joined:
    May 23, 2006
    Messages:
    5,652
    Likes Received:
    1
    Location:
    KS
    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.
     
  5. Scream_Phoenix

    Scream_Phoenix Handsome Boy Model

    Joined:
    May 31, 2001
    Messages:
    45,110
    Likes Received:
    156
    Location:
    Capital City
    thank you you guys. i think im on the right track now.
     

Share This Page