AVERAGE IF combined with SUMPRODUCT

[brooksc29]

I am trying to find the average for a column using sumproduct so the user can choose between a date range within the data set.

The data set looks something like this:

<TABLE style="WIDTH: 196pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=262><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4498" width=123><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 56pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 height=20 width=75></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 92pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 width=123></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 height=20> V</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65> W</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65> X</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 height=20>DATE</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67>PTPA</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67>PITCHES SEEN</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20>4/1/2011</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>18</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>155</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20>4/2/2011</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>17</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>151</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20>4/3/2011</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>13</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>127</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20>4/4/2011</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>14</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>139</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20>4/5/2011</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>8</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>110</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20>4/6/2011</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>18</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>128</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20>4/9/2011</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>33</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>178</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20>4/9/2011</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>33</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>178</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20>4/10/2011</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>11</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>135</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20>4/11/2011</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>8</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>119</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20>4/12/2011</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>16</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>135</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20>4/13/2011</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>13</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>147</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20>4/15/2011</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>15</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>160</TD></TR></TBODY></TABLE>

the user will choose a date range and I'd like to figure out the average in column X (Pitches Seen) between those date ranges.

The V column (Date) is named Dates and the X column (Pitches Seen) is named PITSEEPERDAY.

I'm not entirely sure if I need SUMPRODUCT here, but please help either way!

 

[barry houdini]

Which version of Excel are you using? In Excel 2007 and later you can use AVERAGEIFS, e.g. with start date in Z2 and end date in Z3

=AVERAGEIFS(PITSEEPERDAY,PitchesSeen,">="&Z2,PitchesSeen,"<="&Z3)

[assumes named ranges are the same size]

or with an "array formula" in earlier excel versions....

=AVERAGE(IF(PitchesSeen>=Z2,IF(PitchesSeen<=Z3,PITSEEPERDAY)))

confirmed with CTRL+SHIFT+ENTER

 

[brooksc29]

I have 2007... I subsititued DATES into the Criteria Range and it's working, thank you!

this is the final formula...

=AVERAGEIFS(PITSEEPERDAY,DATES,">="&StartDate,DATES,"<="&EndDate)