Mike, the shorter version works, too. Again, after I entered it I revised it
to A28 instead of A9 and forgot to press Ctrl+Shift+Enter again! My
apologies. Thank you. Connie

"Mike H" wrote:

A bit shorter

=IF(COUNTIF(A42:A5000,A9),AVERAGE(IF((A42:A5000)=A 9,IF(J42:J5000<"",J42:J5000))),"")

Mike

"Mike H" wrote:

Maybe

=IF(ISERROR(AVERAGE(IF((A42:A5000)=A9,IF(J42:J5000 <"",J42:J5000)))),"",AVERAGE(IF((A42:A5000)=A9,IF (J42:J5000<"",J42:J5000))))

Once again Array entered with CTRL+Shift+Enter

"Connie Martin" wrote:

Thank you, Mike. I have a further question to both of you.....In A42:A5000
there might not be the date in A9. In that case, I get the error #DIV/0!
How do I fix that?

Connie

"Mike H" wrote:

Hi,

This will include zeroes in the number range but ignore blanks

=AVERAGE(IF((A42:A5000)=A9,IF(J42:J5000<"",J42:J5 000)))

Array entered with CTRL+Shift+Enter


Mike

"Connie Martin" wrote:

I am stumped. How in the world do I write this formula?

In a cell, I want it to find the date in A9 in A42:A5000 and then average
the numbers in J42:J5000 for that date. Hope this is clear. Many thanks in
advance.
Connie