How to Average a Sumproduct() formula

V

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

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

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
 
J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top