Formula for computing total amount per day

Question

I have this Excel spreadsheet where in one of the columns I need to come up with a formula that will compute the total amount for a given day. This is a small snapshot of a huge spreadsheet:

Date/Time   Time    Amount      Balance       Total $ per day
9/30/2014   5:30 PM $5.20       $87.81  
9/30/2014   4:23 PM $0.14       $93.01  
9/30/2014   8:53 AM ($4.61)    $93.15   
9/30/2014   7:53 AM $5.20       $88.54  
9/29/2014   5:08 PM $1.09       $93.74  
9/29/2014   7:18 AM $1.09       $94.83  
9/26/2014   5:31 PM ($4.61)     $95.92  

For this example the Total $ per day would be:

E2 $5.93 
E6 $2.18 
E8 ($4.61)

I'm guessing there is a formula that is much better than manually computing this by =SUM(X#:X#)?

Answer 1

In E2, put in the following formula:

=IF(A2<>A1,SUMIF(A:A,A2,C:C),"")

Then drag it down.

Basically, you're saying:

• IF: There's a new date in column A (not the same as the date above it),
• THEN: Sum all the values from column C that have the same value in column A
• ELSE: Return an empty cell

That should do the trick.

Answer 2

If $87.81 is in D2 please try, in E2 copied down to suit:

=SUMIF(A:A,A2,C:C)  

This assumes your dollar values are numeric formatted as currency.

The formulae will return a value for each row, even where there are several rows for the same date and a PivotTable may be preferable. Date/Time for ROWS, and Sum of Amount for Σ VALUES. This assumes Date/Time and Time are distinct columns.

For a PT the data need not be sorted in order.