Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Sum Column Based On Another Column

  1. #1
    Join Date
    9th February 2007
    Posts
    18

    Sum Column Based On Another Column

    Hello All,
    Here's what I want to do:

    I have 2 columns, one is "time" and the other column is "code". Time is basically time (0:30, 0:40, 0:45, and so on). Code is a number between 1 and 6. What I want to do is calculate the total time from the time column based on the code from the code column.

    For instance, I would like to find out what the total time for code 1 is, the total time for code 2, and so on.

    I am planning to have this as a chart in a new worksheet.

    Let me know how I would do this.

    Thanks in advance. Appreciate your feedback.

    Regards,

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    Re: Calculating Sum Of One Column Based On Value From Another Column

    Hi ta,

    A simple solution is to use a 'sumif' formula for example:

    Codes in range A1:A6
    Times in range B1:B6

    Formula for Code 1: =SUMIF(A1:A6,1,B1:B6)
    Look in cells A1 to A6 for value "1"
    Add all in Range B1 to B6 that have a value of 1 in the row (in Col A)

    Formula for Code 2: =SUMIF(A1:A6,2,B1:B6)
    Formula for Code 3: =SUMIF(A1:A6,3,B1:B6)
    etc

    Cheers,

    dr

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    9th February 2007
    Posts
    18

    Re: Calculating Sum Of One Column Based On Value From Another Column

    Thanks dr!

    I tried sumif before, but I had the wrong logic. Your explanation was just perfect. It works now.

    Thanks again!

    Regards,

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    9th February 2007
    Posts
    18

    Re: Calculating Sum Of One Column Based On Value From Another Column

    Hello dr,
    Your code works fine, however, when the time column has value over 1 hour. For instance, if start time is 6:00 and end time is 7:00, then my elapsed time is 1:00 (hour). In this case, it has to enter 60 minutes, but the code doesn't.

    Is there a way to accomodate this?

    Again, your code works for minutes between 0:01 and 0:59, but when it becomes an hour (1:00 and over) there seems to be a problem.

    Please let me know what to do.

    Appreciate your help!

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Calculating Sum Of One Column Based On Value From Another Column

    trueassassin, Your original question does not mention start times and end times, so you need to start a new thread to question this new problem.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 3 users browsing this thread. (0 members and 3 guests)

Possible Answers

  1. Replies: 6
    Last Post: March 19th, 2009, 11:24
  2. Auto Fill Column B Based Contents Of Column A
    By tallkoolone in forum EXCEL HELP
    Replies: 4
    Last Post: April 19th, 2007, 22:58
  3. Replies: 8
    Last Post: August 17th, 2006, 23:24
  4. Replies: 4
    Last Post: December 10th, 2004, 03:42
  5. Replies: 5
    Last Post: August 7th, 2003, 21:39

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno