Averageifs & Countifs

[Stephanie]

I am trying to get the average of a multiple cell range. The criteria range
and sum range are not the same size. Does countifs work the same way
regarding this?
Here is the formula I am trying to rearrange to work.
=AVERAGEIFS(H15:H350,B15:B350, ">=01/01/07", B15:B350, "<=01/31/07"). This
one does not give me a true average because I am averaging an average.
=COUNTIFS($C$15:$G$350, J1, $B$15:$B$350, ">=01/01/07", $B$15:$B$350,
"<=01/31/07"). I am trying to count a number in the c - g range using a date
criteria.
Can you help?

 

[Roger Govier]

Hi Stephanie

I don't fully understand your question.
=AVERAGEIFS(H15:H350,B15:B350, ">=01/01/07", B15:B350, "<=01/31/07")
returns a correct value for me.

=COUNTIFS($C$15:$G$350, J1, $B$15:$B$350, ">=01/01/07", $B$15:$B$350,
"<=01/31/07")
has an extra term that is superfluous, ,J1,

=COUNTIFS($C$15:$G$350, $B$15:$B$350, ">=01/01/07", $B$15:$B$350,
"<=01/31/07")
works just fine.

 

[Stephanie]

What I am trying to do is average numbers in 5 columns based on the date in
another column. Is this possible? At that point the numbers are not averaged
yet.

I originally couldn't get the averageifs to work because my ranges were not
the same size - ie: c15:g350 and b15:b350 so I created an average of the 5
columns - ie: c15:c350 and b15:b350. This did not give me a correct value
either because I was averaging something that had already been averaged.

I then tried using countifs but I still can't get it to average correctly.

 

[Roger Govier]

Hi Stephanie

Perhaps you could use
=SUMPRODUCT((B15:B350>=--"01/01/07")*(B15:B350<=--"31/01/07")*(C15:G350))/
SUMPRODUCT((B15:B350>=--"01/01/07")*(B15:B350<=--"31/01/07")/5

If you are always going to be dealing with a whole month, then this can
be simplified to

=SUMPRODUCT((MONTH(B15:B350)=7)*(C15:G350))/
SUMPRODUCT((MONTH(B15:B350)=7)/5