Summing Pivot Table Data Conditionally

[kskapin]

Hello! I have the following data in a Pivot Table and I am looking to sum the Hires by Month by each month. For example, I want a something labeled January and then the sum to equal 6. Ideally, it would be in a summary table on a different sheet, so I'd like to deal with formulas if possible.

I've tried various SUMIF equations to no luck:

=SUMIFS(M9:M21,L9:L21,AND(L9:L21>=DATE(2012,1,1),L9:L21<=DATE(2012,3,31)))

=SUMIF(L9:L21, "1*", M9:M21)

Excel 2010
<TABLE style="BORDER-BOTTOM: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH></TH><TH>L</TH><TH>M</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD></TD><TD style="TEXT-ALIGN: center">Hires by Month</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Yes</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>1/2/2012</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>1/9/2012</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>1/16/2012</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>1/21/2012</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>2/6/2012</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>2/13/2012</TD><TD style="TEXT-ALIGN: center">2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>2/27/2012</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD>3/5/2012</TD><TD style="TEXT-ALIGN: center">2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD>3/12/2012</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD>3/19/2012</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD>3/26/2012</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD>4/2/2012</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD>4/9/2012</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD>Grand Total</TD><TD style="TEXT-ALIGN: center">21</TD></TR></TBODY></TABLE>

Pivot Table​

Any suggestions?

 

[Domski]

Try right clicking on your dates and then Group By and select months and years.

Dom

 

[kskapin]

Thanks Dom, but I don't have a "Group By" option. Just "Group..."

 

[Domski]

Try that then, I was typing from memory.

Dom

 

[kskapin]

Haha... thanks Dom! It does group the cells (as it should) but then no filtering can be applied.

Ideally, I would like a table to look like this, with formulas in N10:Q10.

Excel 2010
<TABLE style="BORDER-BOTTOM: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH></TH><TH>N</TH><TH>O</TH><TH>P</TH><TH>Q</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>January</TD><TD>February </TD><TD>March</TD><TD>April</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">4</TD></TR></TBODY></TABLE>