Averageifs & Countifs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stephanie - 12 Jun 2007 21:08 GMT

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?

Reply to this Message

Roger Govier - 13 Jun 2007 01:14 GMT

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.

Signature

Regards

Roger Govier

>I am trying to get the average of a multiple cell range. The criteria
>range
[quoted text clipped - 9 lines]
> criteria.
> Can you help?

Reply to this Message

Stephanie - 13 Jun 2007 03:47 GMT

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.

> Hi Stephanie
>
[quoted text clipped - 22 lines]
> > criteria.
> > Can you help?

Reply to this Message

Roger Govier - 13 Jun 2007 12:15 GMT

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

Signature

Regards

Roger Govier

> What I am trying to do is average numbers in 5 columns based on the
> date in
[quoted text clipped - 46 lines]
>> > criteria.
>> > Can you help?

Reply to this Message