Formula help - sum, average?

S

SarahJ

New Member
Joined
Sep 1, 2011
Messages
1
Having a heck of time figurering out how to do this, any help, much appreciated. I have a years worth of data entered, I want to sum up each month and then find an average for that month (and then quarter). So I need to sum selected cells in two columns and then divide by the number of rows. It seems simple enough, but I am totally stumped, everything I try ends up in error. Anyone have a formula for this?

Thanks,
Sarah
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
welcome to the board

Easy way to do this, create an extra column that reports just the month for each row, using e.g. =MONTH(...)

Then use SUMIF(month column, month required, values) and COUNTIF(month column, month required) to get your sum and count, divide one by the other for your answer

You can then simply add up the amounts for each quarter in the same way
 
Upvote 0
Try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Date</td><td style="text-align: center;;">Value</td><td style="text-align: right;;"></td><td style="text-align: center;;">Month</td><td style="text-align: center;;">Sum</td><td style="text-align: center;;">Average</td><td style="text-align: right;;"></td><td style="text-align: center;;">Quarter</td><td style="text-align: center;;">Average</td><td style="text-align: center;;">Sum</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">1/1/2011</td><td style="text-align: center;;">275</td><td style="text-align: right;;"></td><td style="text-align: center;;">Jan-11</td><td style="text-align: center;;"> 2,623.00 </td><td style="text-align: center;;"> 655.75 </td><td style="text-align: right;;"></td><td style="text-align: center;;">Q01</td><td style="text-align: center;;"> 5,198.00 </td><td style="text-align: center;;"> 519.80 </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">1/10/2011</td><td style="text-align: center;;">864</td><td style="text-align: right;;"></td><td style="text-align: center;;">Feb-11</td><td style="text-align: center;;"> 1,189.00 </td><td style="text-align: center;;"> 396.33 </td><td style="text-align: right;;"></td><td style="text-align: center;;">Q02</td><td style="text-align: center;;"> 6,445.00 </td><td style="text-align: center;;"> 585.91 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">1/19/2011</td><td style="text-align: center;;">634</td><td style="text-align: right;;"></td><td style="text-align: center;;">Mar-11</td><td style="text-align: center;;"> 1,386.00 </td><td style="text-align: center;;"> 462.00 </td><td style="text-align: right;;"></td><td style="text-align: center;;">Q03</td><td style="text-align: center;;"> 5,062.00 </td><td style="text-align: center;;"> 506.20 </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">1/28/2011</td><td style="text-align: center;;">850</td><td style="text-align: right;;"></td><td style="text-align: center;;">Apr-11</td><td style="text-align: center;;"> 1,871.00 </td><td style="text-align: center;;"> 467.75 </td><td style="text-align: right;;"></td><td style="text-align: center;;">Q04</td><td style="text-align: center;;"> 3,640.00 </td><td style="text-align: center;;"> 364.00 </td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">2/6/2011</td><td style="text-align: center;;">451</td><td style="text-align: center;;"></td><td style="text-align: center;;">May-11</td><td style="text-align: center;;"> 2,024.00 </td><td style="text-align: center;;"> 674.67 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">2/15/2011</td><td style="text-align: center;;">407</td><td style="text-align: center;;"></td><td style="text-align: center;;">Jun-11</td><td style="text-align: center;;"> 2,550.00 </td><td style="text-align: center;;"> 637.50 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">2/24/2011</td><td style="text-align: center;;">331</td><td style="text-align: center;;"></td><td style="text-align: center;;">Jul-11</td><td style="text-align: center;;"> 1,618.00 </td><td style="text-align: center;;"> 539.33 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">3/5/2011</td><td style="text-align: center;;">538</td><td style="text-align: center;;"></td><td style="text-align: center;;">Aug-11</td><td style="text-align: center;;"> 1,934.00 </td><td style="text-align: center;;"> 644.67 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">3/14/2011</td><td style="text-align: center;;">377</td><td style="text-align: center;;"></td><td style="text-align: center;;">Sep-11</td><td style="text-align: center;;"> 1,510.00 </td><td style="text-align: center;;"> 377.50 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">3/23/2011</td><td style="text-align: center;;">471</td><td style="text-align: center;;"></td><td style="text-align: center;;">Oct-11</td><td style="text-align: center;;"> 1,094.00 </td><td style="text-align: center;;"> 364.67 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">4/1/2011</td><td style="text-align: center;;">333</td><td style="text-align: center;;"></td><td style="text-align: center;;">Nov-11</td><td style="text-align: center;;"> 1,427.00 </td><td style="text-align: center;;"> 356.75 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">4/10/2011</td><td style="text-align: center;;">351</td><td style="text-align: center;;"></td><td style="text-align: center;;">Dec-11</td><td style="text-align: center;;"> 1,119.00 </td><td style="text-align: center;;"> 373.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">4/19/2011</td><td style="text-align: center;;">720</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">4/28/2011</td><td style="text-align: center;;">467</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet9</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">MONTH(<font color="Green">A$2:A$42</font>)=MONTH(<font color="Green">D2</font>)</font>),B$2:B$42</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">MONTH(<font color="Green">A$2:A$42</font>)=MONTH(<font color="Green">D2</font>)</font>),B$2:B$42</font>)/SUMPRODUCT(<font color="Blue">--(<font color="Red">MONTH(<font color="Green">A$2:A$42</font>)=MONTH(<font color="Green">D2</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">(<font color="Green">INT(<font color="Purple">(<font color="Teal">MONTH(<font color="#FF00FF">A$2:A$42</font>)-1</font>)/3</font>)+1</font>)=RIGHT(<font color="Green">H2,1</font>)*1</font>),B$2:B$42</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">(<font color="Green">INT(<font color="Purple">(<font color="Teal">MONTH(<font color="#FF00FF">A$2:A$42</font>)-1</font>)/3</font>)+1</font>)=RIGHT(<font color="Green">H2,1</font>)*1</font>),B$2:B$42</font>)/SUMPRODUCT(<font color="Blue">--(<font color="Red">(<font color="Green">INT(<font color="Purple">(<font color="Teal">MONTH(<font color="#FF00FF">A$2:A$42</font>)-1</font>)/3</font>)+1</font>)=RIGHT(<font color="Green">H2,1</font>)*1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz
 
Upvote 0
SarahJ said:
Having a heck of time figurering out how to do this, any help, much appreciated. I have a years worth of data entered, I want to sum up each month and then find an average for that month (and then quarter). So I need to sum selected cells in two columns and then divide by the number of rows. It seems simple enough, but I am totally stumped, everything I try ends up in error. Anyone have a formula for this?

Thanks,
Sarah
Click to expand...
Are the dates all within the same year?

Are the quarters normal calendar quarters?

What version of Excel are you using?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Average across multiple worksheets excluding unfilled cells
Replies
4
Views
205
Domenic
D
S
  • Question Question
AVERAGEIF TROUBLES
Replies
3
Views
77
AhoyNC
A
M
  • Question Question
Pivot Table Calculated Field Average and Std Deviation (control chart)
Replies
4
Views
265
Sergius
S
S
  • Solved
average with conditions
Replies
6
Views
191
Asbestos_Jen
Asbestos_Jen
O
  • Solved
Contribution to an overall % growth (Math Question)
Replies
2
Views
75
onthegreen03
O

Forum statistics

Threads
1,224,590
Messages
6,179,750
Members
452,940
Latest member
rootytrip

Share this page

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?

Disable AdBlock

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
Back
Top