# Formula to sum with criteria from a custom formatted cell

#### patriot

##### New Member
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>

Last edited:

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### FDibbins

##### Well-known Member
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

##### MrExcel MVP
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)

Last edited:

#### FDibbins

##### Well-known Member
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

##### MrExcel MVP
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

##### Well-known Member
Good to know, thanks for the feedback

#### Tetra201

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
... 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

##### New Member
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?

Replies
1
Views
142
Replies
1
Views
289
Replies
3
Views
58
Replies
7
Views
171
Replies
2
Views
291

1,190,700
Messages
5,982,371
Members
439,776
Latest member
mathewduffy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back