Averageifs & Countifs
 

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?

Averageifs & Countifs
 

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.
--
Regards

Roger Govier


"Stephanie" wrote in message
...
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?

Averageifs & Countifs
 

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" wrote:

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.
--
Regards

Roger Govier


"Stephanie" wrote in message
...
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?

Averageifs & Countifs
 

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
--
Regards

Roger Govier


"Stephanie" wrote in message
...
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" wrote:

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.
--
Regards

Roger Govier


"Stephanie" wrote in message
...
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?