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>

 

[FDibbins]

Maybe something like this?

=sumifS($D$2:$D$50000,$C$2:$C$50000,">=9/1/2016",$C$2:$C$50000,"<=9/30/2016")

OR you could put the 1st date in its own cells and reference it...
=sumifS($D$2:$D$50000,$C$2:$C$50000,">="&$A$1,$C$2:$C$50000,"<"&edate($A$1,1))

 

[Tetra201]

As an alternative, you can also use the following formula:

=SUMPRODUCT(--(YEAR($C$2:$C$50000)=2011),--(MONTH($C$2:$C$50000)=9),$D$2:$D$50000)

 

[FDibbins]

As an alternative, you can also use the following formula:

=SUMPRODUCT(--(YEAR($C$2:$C$50000)=2011),--(MONTH($C$2:$C$50000)=9),$D$2:$D$50000)

Might be a little slow with "thousands" or rows

 

[Tetra201]

Might be a little slow with "thousands" or rows

Just tested both formulas on a C2:D50000 range -- did not notice any difference in response time.
In both cases the result shows up immediately after hitting Enter.

 

[FDibbins]

Good to know, thanks for the feedback

 

[Tetra201]

Good to know, thanks for the feedback

Wait, here are the results of further testing on the same range:

=SUMPRODUCT(--(YEAR($C$2:$C$50000)=$E2),--(MONTH($C$2:$C$50000)=$F2),$D$2:$D$50000)
20% completion ~120 sec

=SUMIFS($D$2:$D$50000,$C$2:$C$50000,">="&$G2,$C$2:$C$50000,"<"&EDATE($G2,1))
20% completion ~40 sec

So, in my test SUMIFS was ~3x faster than SUMPRODUCT.

 

[patriot]

I tried the formula "=SUMPRODUCT(--(YEAR($C$2:$C$50000)=2011),--(MONTH($C$2:$C$50000)=9),$D$2:$D$50000)" modified for (4) rows and got "#VALUE!" which was the problem I was having when trying formulas myself...I thought it had something to do with the custom format in column C.

Any ideas to make it work?

 

[Tetra201]

... got "#VALUE!"...

This can happen if your column C has a non-date string or a #VALUE! error.
Did you try FDibbins' SUMIFS formula? -- it ignores all strings and errors.

 

[patriot]

Just tried "=sumifS($D$2:$D$50000,$C$2:$C$50000,">=9/1/2016",$C$2:$C$50000,"<=9/30/2016")" for a couple different months and years and thought all was good...then I checked the totals and discovered the amounts in the last day of the month was not included in the sum.

I do appreciate your help...any more ideas?