Excel 2003 Function Help

I need help coming up will a function that will average a range in one column
based on if the hours match. Here is my set up.

-------A-------B
1.....09:21....12
2.....09:46....24
3.....09:57....17
4.....10:04....22

I need to average column B if column A's hour is the same as the hour in a
cell in another worksheet.

Ryan Hicks wrote:
I need help coming up will a function that will average a range in one column
based on if the hours match. Here is my set up.

-------A-------B
1.....09:21....12
2.....09:46....24
3.....09:57....17
4.....10:04....22

I need to average column B if column A's hour is the same as the hour in a
cell in another worksheet.

= SUMIF(range,criteria,sum_range) / COUNTIF(range,criteria)

Right, how would it work if I am looking for every value that is in the nine
o-clock hour. If the criteria range has 09:00, then it will not look for
values other than 09:00. Thank you

"Glenn" wrote:

Ryan Hicks wrote:
I need help coming up will a function that will average a range in one column
based on if the hours match. Here is my set up.

-------A-------B
1.....09:21....12
2.....09:46....24
3.....09:57....17
4.....10:04....22

I need to average column B if column A's hour is the same as the hour in a
cell in another worksheet.

= SUMIF(range,criteria,sum_range) / COUNTIF(range,criteria)
.

Then you would use SUMPRODUCT. Look at these two links for help on counting and
summing, respectively, cells that match multiple criteria (in your case greater
than/or equal to one time -and- less than another time):

http://www.contextures.com/xlFunctio...tml#SumProduct

http://www.contextures.com/xlFunctio...tml#SumProduct


Ryan Hicks wrote:
Right, how would it work if I am looking for every value that is in the nine
o-clock hour. If the criteria range has 09:00, then it will not look for
values other than 09:00. Thank you

"Glenn" wrote:

Ryan Hicks wrote:
I need help coming up will a function that will average a range in one column
based on if the hours match. Here is my set up.

-------A-------B
1.....09:21....12
2.....09:46....24
3.....09:57....17
4.....10:04....22

I need to average column B if column A's hour is the same as the hour in a
cell in another worksheet.
= SUMIF(range,criteria,sum_range) / COUNTIF(range,criteria)
.

Hi,

You could try this

=sumproduct((hour($A$1:$A$4)=hour(A10))*($B$1:$B$4 ))/sumproduct(1*(hour($A$1:$A$4)=hour(A10))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Ryan Hicks" wrote in message
...
I need help coming up will a function that will average a range in one
column
based on if the hours match. Here is my set up.

-------A-------B
1.....09:21....12
2.....09:46....24
3.....09:57....17
4.....10:04....22

I need to average column B if column A's hour is the same as the hour in a
cell in another worksheet.