locked
Calculate sum based on two critera RRS feed

  • Question

  • Hi,

    I have an Excel sheet that has two columns that I am using Data validation from a list. The first one is names and the second is hours. I need to do a running total of the hours based on the names. So if p1 does 4, 3 and 7 hrs then I need to get the total (14), and if p2 does 2, 3, and 1 then I need a seperate total (6).

    Hopefully someone can help me out with this


    Thanks, Conor
    Tuesday, January 17, 2012 1:47 PM

Answers

  • Let's say you have data in A2:B1000, and that you enter the names p1, p2 etc. in K2, K3 etc.

    In L2, enter

    =SUMIF($A$2:$A$1000,K2,$B2:$B$1000)

    and fill down as far as needed.


    Regards, Hans Vogelaar
    • Marked as answer by conwal Tuesday, January 17, 2012 7:52 PM
    Tuesday, January 17, 2012 2:35 PM

All replies

  • Let's say you have data in A2:B1000, and that you enter the names p1, p2 etc. in K2, K3 etc.

    In L2, enter

    =SUMIF($A$2:$A$1000,K2,$B2:$B$1000)

    and fill down as far as needed.


    Regards, Hans Vogelaar
    • Marked as answer by conwal Tuesday, January 17, 2012 7:52 PM
    Tuesday, January 17, 2012 2:35 PM
  • Hi Hans,

    I tried that but it didn't work.

    In column B, I have names B1 to B100

    In column C, I have hours, from 1 to 8

    So that I am able to sum the hours for each person based on there name

    I have the peoples actual names (just their fornames) in the data validation list for column B and the hours in the data validation list in column C


    Thanks, Conor
    • Edited by conwal Tuesday, January 17, 2012 7:47 PM
    Tuesday, January 17, 2012 7:43 PM
  • Hi Hans,

    I just played with it and added "" and it works.

    =SUMIF(B1:B5,"ds",C1:C5)

     

    Thanks for your help


    Thanks, Conor
    Tuesday, January 17, 2012 7:52 PM