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,

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,

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
__

Thanks a lot Richard,

This does it!

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?

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?