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]

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?

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

should not miss anything between these dates.

Note that we can have the date criterion in a cell of its own.

X2: 2016-09-01

Then:

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

 

[Tetra201]

As I pointed out, SUMIFS ignores all strings. So, if some of your dates in column C are text strings (not numbers), the corresponding amounts will not be included.

On the other hand, SUMPRODUCT does work with date strings, but it is susceptible to non-dates and errors.

So, try to tidy up you data, and both formulas should work.

 

[FDibbins]

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.

Again, thanks for the feedback, I figured SP was slower

 

[patriot]

I tried this formula "=SUMIFS(D:D,C:C,">="&DATE(2016,9,1),C:C,"<="&EOMONTH(DATE(2016,9,1),0))" for January 2012 and 1/31/2012 was not included in the total. Last (2) rows are formatted the same as cells above.

Any other suggestions?
..........C.............D
1/1/2012 18:21 14.99
1/1/2012 18:21 5.99
1/6/2012 21:21 5.99
1/8/2012 10:27 24.99
1/11/2012 8:01 19.99
1/21/2012 22:13 19.99
1/23/2012 13:11 14.99
1/24/2012 20:25 24.99
1/25/2012 19:17 16.99
1/25/2012 21:48 12.99
1/27/2012 16:17 66.99
1/28/2012 9:31 69.99
1/28/2012 18:48 49.99
1/31/2012 19:54 3.99
1/31/2012 19:55 3.99

 

[FDibbins]

Check to make sure those are actually dates, and not text looking like dates - test with =isnumber(cell-ref)

 

[Aladin Akyurek]

I tried this formula "=SUMIFS(D:D,C:C,">="&DATE(2016,9,1),C:C,"<="&EOMONTH(DATE(2016,9,1),0))" for January 2012 and 1/31/2012 was not included in the total. Last (2) rows are formatted the same as cells above.

Any other suggestions?
..........C.............D
1/1/2012 18:21 14.99
1/1/2012 18:21 5.99
1/6/2012 21:21 5.99
1/8/2012 10:27 24.99
1/11/2012 8:01 19.99
1/21/2012 22:13 19.99
1/23/2012 13:11 14.99
1/24/2012 20:25 24.99
1/25/2012 19:17 16.99
1/25/2012 21:48 12.99
1/27/2012 16:17 66.99
1/28/2012 9:31 69.99
1/28/2012 18:48 49.99
1/31/2012 19:54 3.99
1/31/2012 19:55 3.99

I believe 1/31/2012 7:54:00 PM is beyond 1/31/2012, that is, it is already 2/1/2012, therefore not included in the total for the month of January 2012. Put otherwise:

1/31/2012 7:54:00 PM <= 1/31/2012 12:00:00 AM

is false.

 

[patriot]

I believe 1/31/2012 7:54:00 PM is beyond 1/31/2012, that is, it is already 2/1/2012, therefore not included in the total for the month of January 2012. Put otherwise:

1/31/2012 7:54:00 PM <= 1/31/2012 12:00:00 AM

is false.

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?

 

[Tetra201]

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?

Try this:

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

 

[patriot]

Try this:

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

Worked perfectly...did a random check and all seems to be correct.

Thank you for your help...and all the others who contributed.

Have a Merry Christmas and Happy and Healthy New Year.

 

[Tetra201]

You are welcome.
Happy holidays.