AverageIfs function only returns first matching value

[LambChoptheKid]

I have a problem with an excel averageifs formula in that it only returns the first matching value, and does not average all values which correspond to the right criteria.

The goal is to average every value which corresponds to a particular hour of the day. I've attached a screenshot below.

For example, for the hour of 1:00 AM I would like to take an average of every value in row C which corresponds to a value of 1:00 AM in row B.

The formula in in G2 is as follows: =AVERAGEIFS(C:C, B:B, F2)

I have had success with using this formula before for similar projects and I cannot figure out why this is not working.

Any assistance will be appreciated.

Thank you

 

[awoohaw]

That was the problem. Just looked into the time values in the lower portions of the data and found that they were stored as dates.

It's always the small errors lol.

Thank you!!

My pleasure, happy to help you find a solution.
Regarding TIME VALUES.... I always enter time as DATETIME. If you have only TIME values and go past midnight on a schedule it drives me crazy, and it ensures consistency.

But in your circumstance you could have gotten matches by using the MOD function:

Mod(DATETIMEVALUE,1) = Mod(Time(1,0,0),1) where the first expression is your column of data and the second is the COUNTIF Time Value.

Best Wishes!