Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Sum Column Based On Another Column

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.

Regards,

Excel Video Tutorials / Excel Dashboards Reports

2. Have VBA, will travel
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. 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. 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.

Excel Video Tutorials / Excel Dashboards Reports

5. Super Moderator
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

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

#### 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