# Calculating totals but eliminating month not complete

#### jeffreybrown

##### Well-known Member
Hi All,

Since we are still in March therefore not complete, how can I keep the \$103 for March from showing up in the average. Is it possible to do the same for the total in I2, not total up March until complete. Lastly, the formula in I15 works fine, but is there another way of writing this formula with Average?

Sheet2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 69px"><COL style="WIDTH: 76px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Oct-08</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">\$520.60 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Nov-08</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">\$243.05 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Dec-08</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">\$273.40 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Jan-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">\$166.78 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Feb-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">\$249.15 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Mar-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">\$103.00 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Apr-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">\$0.00 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">May-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">\$0.00 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Jun-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">\$0.00 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Jul-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">\$0.00 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Aug-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">\$0.00 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Sep-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">\$0.00 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-FAMILY: Calibri"> </TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">\$1,555.98 </TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Total to date</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-FAMILY: Calibri"> </TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">\$259.33 </TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Average</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="FONT-FAMILY: Calibri"> </TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">\$3,111.96 </TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Projection</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>I2</TD><TD>=SUMPRODUCT(--(TEXT(\$B\$3:\$B\$55,"mmmyyyy")=TEXT(H2,"mmmyyyy")),(\$E\$3:\$E\$55))</TD></TR><TR><TD>I14</TD><TD>=SUM(I2:I13)</TD></TR><TR><TD>I15</TD><TD>=SUM(I2:I13)/COUNTIF(I2:I13,"<>0")</TD></TR><TR><TD>I16</TD><TD>=I15*COUNTA(H2:H13)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### barry houdini

##### MrExcel MVP
You could alter I2 like this

=IF(TODAY() >H2+31-DAY(H2+31),SUMPRODUCT(--(TEXT(\$B\$3:\$B\$55,"mmmyyyy")=TEXT(H2,"mmmyyyy")),(\$E\$3:\$E\$55)),"")

In that way you won't see the March total until 1st April, and all upcoming months will be blank so you can just use this formula for I15

=AVERAGE(A2:A13)

#### mikerickson

##### MrExcel MVP
This uses a Named Value for clarity, but the formula for the name can be replaced into the spreadsheet formula if that style is prefered.

Name: LastOfPreviousMonth
RefersTo: =TODAY()-DAY(TODAY())
<table border=1 cellspacing = 0 bgcolor="#ffffff"><tr bgcolor = "#aaaaaa"><td> <td align=center width=85><b>H</b><td align=center width=85><b>I</b><td align=center width=85><b>J</b><td align=center width=85><b>K</b>
<tr><td align=center bgcolor="#aaaaaa"><b>2</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Oct-08</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">\$520.60 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>3</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Nov-08</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">\$243.05 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>4</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Dec-08</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">\$273.40 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>5</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Jan-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">\$166.78 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>6</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Feb-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">\$249.15 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>7</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Mar-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">\$103.00 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>8</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Apr-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">\$0.00 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>9</b><td bgcolor="#FFFFFF" > <FONT color="#000000">May-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">\$0.00 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>10</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Jun-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">\$0.00 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>11</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Jul-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">\$0.00 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>12</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Aug-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">\$0.00 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>13</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Sep-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">\$0.00 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>14</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">\$1,555.98 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Total to date</FONT><td bgcolor="#CCFFFF" > <FONT color="#000000">\$1,452.98 </FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>15</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">\$259.33 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Average</FONT><td bgcolor="#CCFFFF" > <FONT color="#000000">\$290.60 </FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>16</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">\$3,111.96 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Projection</FONT><td bgcolor="#CCFFFF" > <FONT color="#000000">\$3,487.15 </FONT></tr></table><table border=1 cellspacing = 0 bgcolor="#ddedcc">
<tr><td colspan=3 align="center">Formulas in this range: </tr>
<tr><td align=center>Range with same formula<td align=center>Cell:<td align=center>holds Formula:</tr>
<tr><td><td align=center>K14 <td align = left >=SUMIF(H2:H13,"<"&LastOfPreviousMonth,I2:I13)</tr>
<tr><td><td align=center>K15 <td align = left >=K14/COUNTIF(H2:H13,"<"&LastOfPreviousMonth)</tr>
<tr><td><td align=center>K16 <td align = left >=K15*COUNTA(\$H\$2:\$H\$13)</tr></table>

#### jeffreybrown

##### Well-known Member
Thanks Mike & Barry...perfect as always

Replies
2
Views
224
Replies
10
Views
780
Replies
0
Views
1K
Replies
4
Views
3K
Replies
0
Views
587

1,195,992
Messages
6,012,739
Members
441,724
Latest member
Aalbid

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

### Which adblocker are you using?

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

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