How to Average a Sumproduct() formula

[Vacuum Sealed]

Hi all

I use the following:

=SUMPRODUCT(--(Historical!$A$2:$A$10000="Jun"),--(Historical!$C$2:$C$10000="Braybrook"),--(Historical!$K$2:$K$10000))

Rather than returning the sum of Column "K", is there a way for the formula
to return the average over the given criteria.

As always

TIA
Mick.

 

[Vacuum Sealed]

Cancel that request

I added another column and inserted the following:

=COUNTA(Historical!$K$3:$K$10000,Historical!$A$3:$A$10000="Jan"&Historical!$B$3:$B$10000="Braybrook")

It returns the number of rows that match so I can then use that number to
divide my sumproduct by.

The interesting thing is that when I start the formula like:

=COUNTA(Historical!$K$2:$K$10000,Historical!$A$2:$A$10000="Jan"&Historical!$B$2:$B$10000="Braybrook")

It returns a value of 4 when I only have 3 rows of data for testing.

The 1st row is the Header, I cleared all the cells below the 3 populated
ones and it still returns 4. Am I missing something here, does the CountA()
count the entire column regardless of specified range or else...??

If I use the 1st formula at the very top of this post, it returns my desired
3 count, even though that is not an accurate reflection of the true range.

TIA
Mick.

 

[Pete_UK]

Going back to your original request, if you want to sum the values
that meet your criteria you can use this:

=SUMPRODUCT(--(Historical!$A$2:$A$10000="Jun"),--(Historical!$C$2:$C
$10000=­"Braybrook"),Historical!$K$2:$K$10000)

and if you want to count the number of times the criteria are met you
can use this:

=SUMPRODUCT(--(Historical!$A$2:$A$10000="Jun"),--(Historical!$C$2:$C
$10000=­"Braybrook"))

So, you can just divide one by the other to get the average.

I don't understand the formulae you quote using COUNTA - you seem to
be trying to set up a condition, but COUNTA doesn't work that way.

If you are using XL2007 or later you can make use of SUMIFS and
COUNTIFS (which allow more than one criteria), and you can even try
AVERAGEIF.

Hope this helps.

Pete

 

[Vacuum Sealed]

Thx Pete

You have cleared it up very nicely.

Cheers
Mick

 

[joeu2004]

if you want to sum the values that meet your criteria
you can use this:
=SUMPRODUCT(--(Historical!$A$2:$A$10000="Jun"),
--(Historical!$C$2:$C$10000=­"Braybrook"),
Historical!$K$2:$K$10000)

and if you want to count the number of times the criteria
are met you can use this:
=SUMPRODUCT(--(Historical!$A$2:$A$10000="Jun"),
--(Historical!$C$2:$C$10000=­"Braybrook"))

So, you can just divide one by the other to get the average.

Alternatively, the following array formula (press ctrl+shift+Enter
instead of Enter):

=AVERAGE(IF(Historical!$A$2:$A$10000="Jun",
IF(Historical!$C$2:$C$10000=­"Braybrook",
Historical!$K$2:$K$10000)))

Or use AVERAGEIFS if you have XL2007 or later.