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,