Formula to sum with criteria from a custom formatted cell

[patriot]

How do I write a formula that will total "D" for a particular month for a particular year. The spreadsheet has thousands of entries for different years and months. The format for the cells in "C" are custom - "m/d/yyyy h:mm". What would the formula be to total "D" for September 2011? Your help is appreciated.

1	C	D
2	6/30/2011 12:00:00 AM​	20.00
3	9/4/2011 5:19:00 PM	6.00
4	9/27/2011 5:19:00 PM	8.00
5	10/1/2011 6:46:00 PM	10.00

<tbody>
</tbody>

 

[Aladin Akyurek]

So...what is the most efficient way to fix this formula "=SUMIFS(D:D,C:C,">="&DATE(2012,1,1),C:C,"<="&EOMONTH(DATE(2012,1,1),0))" to include amounts for all of January until 2/1/2012?

If doing that is well motivated...

Either:

=SUMPRODUCT($D$1:$D$16,(INT($C$1:$C$16)-DAY($C$1:$C$16)+1=DATE(2012,1,1))+0)

Or:

=SUMPRODUCT($D$1:$D$16,(INT($C$1:$C$16)-DAY($C$1:$C$16)+1="2012-01-01"+0)+0)

Or, staying with SUMIFS()...

=SUMIFS(D:D,C:C,">="&DATE(2012,1,1),C:C,"<="&EOMONTH(DATE(2012,1,1),0)+1)

If efficiency is a concern, set X2 (or any other cell) to 2012-01-01, and invoke:

=SUMIFS(D:D,C:C,">="&X2,C:C,"<="&EOMONTH(X2,0)+1)