Excel 2007 : sum column matching dates excluding time

**luix** · 02-02-2011, 03:27 PM

Hi all,

Third new post for me.

I have two columns. One with dates including the time stamp on them. The other with amounts for those dates.

Date Result
Sun, Jan 30, 2011, 3 AM $289.78
Sun, Jan 30, 2011, 7 AM $290.78
Sun, Jan 30, 2011, 11 AM $231.34
Mon, Jan 31, 2011, 3 PM $23.78
Mon, Jan 31, 2011, 7 PM $143.12

How can I add all the amounts posted on Jan 30 regardless of the time?
For example, for Jan 30 I should add
$289.78
$290.78
$231.34

Also note that the list of dates and amounts includes zeros.

Thanks in advance.

luix

**DonkeyOte** · 02-02-2011, 03:32 PM

Adapt the COUNTIFS formula from the prior thread - ie SUMIFS

Please Login or Register  to view this content.

Others might suggest Pivots (Group by Day) - whether or not that's viable depends on how you intend to update the data and use the results.

**Cutter** · 02-02-2011, 03:45 PM

And for the SUMPRODUCT() fans (I'm not one)

=SUMPRODUCT((INT(B3:B8)=E3)*(C3:C8))

**DonkeyOte** · 02-02-2011, 03:55 PM

Cutter, if using SUMPRODUCT I'd suggest

Please Login or Register  to view this content.

to remove potential for coercion issues

**Cutter** · 02-02-2011, 03:57 PM

told you I wasn't a fan.

**luix** · 02-02-2011, 06:31 PM

Once again I am impressed with the accuracy and speed of the responses.

Thank you again DonkeyOte!

Thank you Cutter.

I'll mark this one solved.

luix