Averageifs formula that goes by date range and one other criteria

[njsutorius]

Hello,

I have pieced together a formula that will average a list of scores if 2 criteria are met. 1) is a id #, and the 2nd is a date range.

The formula is as follows: =AVERAGEIFS(Sheet2!H:H,Sheet2!D:Sheet2!D,C10,Sheet2!G:G,">="&D5,Sheet2!G:G,"<="&D6)

C10 = the agent id #
sheet2!H:H is the list of scores
Shhet2 D:D is the agent id's
Sheet2G:G is the date range
D5 and D6 are the to and from dates

When i run the formula i get a #VALUE result but i cant figure out why.

I was messing around with it so much that the year part of the dates somehow got changed from 2013 and 2014 to 2113 and 2114. When they changed to the 2114/2113 the formula all of a sudden worked! I used find/replace to change the dates back to 2013 and 2014 both on the date range and the reference cells, but once again it returns to a #value response. what am i doing wrong? I tried changing the dates back to 2114/2113 and it failed to work.

I attempted to format the cells for the date range and specifically chose a date format to see if this would resolve the issue but it did not.

Appreciate any help.

Thanks
Nate

 

[jim may]

Why is this: Sheet2!D:Sheet2!D

not Sheet2!D:D ?

 

[Weazel]

is the data on sheet 2 populated by formulas or pasted, particularly the scores in column H

if it comes from formulas you might want to check for any value errors in the data

 

[njsutorius]

Why is this: Sheet2!D:Sheet2!D

not Sheet2!D:D ?

Thats a darn good question, and exactly the reason i came to this forum.

Thanks for solving my issue. Ill be sure to bug you as my project moves along.

 

[njsutorius]

is the data on sheet 2 populated by formulas or pasted, particularly the scores in column H

if it comes from formulas you might want to check for any value errors in the data

Good advice. Ill be sure to keep it in mind as i can easily see how that could occur.