Excel Sum Based on Several Criteria

Question

   A         B       C            

1  10/1/2009 3652449 12:15:43 AM
2  10/1/2009 3652449 12:17:03 AM
3  10/4/2009 3652449 1:03:08 AM

Hello,

I want to sum the total time of the card 3652449 just for 10/1/2009.

Then after the sum is compete I need the time to be converted and rounded into minutes only.

I have been using the following formula till now " =SUMIF(B:B,3652449,C:C) " and the result is " 1:35:54 ". But as I have written I want to sum the time for that card only on the date of 10/1/2009. When you copy the data in excel and you manually sum the time from 10/1/2009 you should get 32m & 46s. When you round it the result should be 32m.

I will be thankful for your help.

Answer 1

I'm still not sure if I got you right.

If you want to add the date as condition, you can try something like that:

Add a formula to your column D: =B1&" "&A1

This should give you the following result:

   A         B       C            D
1  10/1/2009 3652449 12:15:43 AM  3652449 40087
2  10/1/2009 3652449 12:17:03 AM  3652449 40087
3  10/4/2009 3652449 1:03:08 AM   3652449 40090

Now you can change your original formula to: =SUMIF(D:D,"3652449 40087",C:C)

You can make column D invisible by either changing width to 0 or changing color to white.

Hope that is what you tried...

Answer 2

You should use a pivot table, which can easily give you the sum for every date in your table.

Pivot tables are supported in all good desktop spreadsheets, but not many online web-based ones.

See http://en.wikipedia.org/wiki/Pivot_table