sumproduct & average
 

Hi,

I am trying to use sumproduct to get an average of a column, based on a
criteria in another column.

In one data sheet, i have:

Name AHT Quality Resolution
------------------------------------------------------
sean 546 90 100
john 342 100 100
john 369 75 98
sean 786 100 100
sean 540 72 76
john 352 95 95


In another consolidated data sheet, i have:

Name AHT Quality Resolution
------------------------------------------------------
Sean
John

what i am trying to do is find an average for john & Sean:

=sumproduct((a:a)=a1,(b:b))/count(b:b)

getting #NUM...

P.S: i donot want to use fixed ranges for both the columns, hence using
(a:a) & (b:b).

I have come across a formula using Average & IF:

=Average(IF(a2:a3500)=a1,(b2:b3500))

but here i get results based on Fixed ranges, which i dont want.

Have tried SumIF also, but using fixed ranges & the average is not
correct....



Can anyone help me with SumProduct.


Rgds,

sumproduct & average
 

You can't use sumproduct or any array formula using the whole range, thus
the error

if indeed you have plus 65000 rows use A1:A65535

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"junoon" wrote in message
oups.com...
Hi,

I am trying to use sumproduct to get an average of a column, based on a
criteria in another column.

In one data sheet, i have:

Name AHT Quality Resolution
------------------------------------------------------
sean 546 90 100
john 342 100 100
john 369 75 98
sean 786 100 100
sean 540 72 76
john 352 95 95


In another consolidated data sheet, i have:

Name AHT Quality Resolution
------------------------------------------------------
Sean
John

what i am trying to do is find an average for john & Sean:

=sumproduct((a:a)=a1,(b:b))/count(b:b)

getting #NUM...

P.S: i donot want to use fixed ranges for both the columns, hence using
(a:a) & (b:b).

I have come across a formula using Average & IF:

=Average(IF(a2:a3500)=a1,(b2:b3500))

but here i get results based on Fixed ranges, which i dont want.

Have tried SumIF also, but using fixed ranges & the average is not
correct....



Can anyone help me with SumProduct.


Rgds,

sumproduct & average
 

Try

=SUMIF(Sheet1!A:A,A3,Sheet1!B:B)/COUNTIF(Sheet1!A:A,A3)

HTH



On 20 May 2006 14:52:40 -0700, "junoon" wrote:

Hi,

I am trying to use sumproduct to get an average of a column, based on a
criteria in another column.

In one data sheet, i have:

Name AHT Quality Resolution
------------------------------------------------------
sean 546 90 100
john 342 100 100
john 369 75 98
sean 786 100 100
sean 540 72 76
john 352 95 95


In another consolidated data sheet, i have:

Name AHT Quality Resolution
------------------------------------------------------
Sean
John

what i am trying to do is find an average for john & Sean:

=sumproduct((a:a)=a1,(b:b))/count(b:b)

getting #NUM...

P.S: i donot want to use fixed ranges for both the columns, hence using
(a:a) & (b:b).

I have come across a formula using Average & IF:

=Average(IF(a2:a3500)=a1,(b2:b3500))

but here i get results based on Fixed ranges, which i dont want.

Have tried SumIF also, but using fixed ranges & the average is not
correct....



Can anyone help me with SumProduct.


Rgds,

RB
__

sumproduct & average
 

Thanks a lot Richard,

This does it!

sumproduct & average
 

Hi Richard,

One small problem!

If in column B or C or D there are 0 values like:


Name AHT Quality Resolution
------------------------------------------------------
sean 546 0 100
john 342 100 0
john 369 75 98
sean 786 100 0
sean 540 0 76
john 0 95 95

Then, How do we get a correct Average?

sumproduct & average
 

If you are on Excel 2003, turn the current range in A:D into a list by
means of Data|List|Create List and invoke using current ranges:

=AVERAGE(IF($A$2:$A$400="sean",IF($B$2:$B$400 0,$B$2:$B$400)))

which needs to be confirmed with control+shift+enter.

If not on Excel 2003...

X2:

=MATCH(9.99999999999999E+307,B2:B65536)

Then invoke:

=AVERAGE(IF($A$2:INDEX($A$2:$A$65536,X2)="sean",IF ($B$2:INDEX($B$2:$B$65536,X2)
0,$B$2:INDEX($B$2:$B$65536,X2))))

Again, completed with control+shift+enter.

junoon wrote:
Hi Richard,

One small problem!

If in column B or C or D there are 0 values like:


Name AHT Quality Resolution
------------------------------------------------------
sean 546 0 100
john 342 100 0
john 369 75 98
sean 786 100 0
sean 540 0 76
john 0 95 95

Then, How do we get a correct Average?