software

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 95In another consolidated data sheet, i have:

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

what i am trying to do is find an average for john amp; 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) amp; (b:b).

I have come across a formula using Average amp; 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 amp; 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
quot;It is a good thing to follow the first law of holes;
if you are in one stop digging.quot; Lord Healeyquot;junoonquot; gt; wrote in message oups.com...
gt; Hi,
gt;
gt; I am trying to use sumproduct to get an average of a column, based on a
gt; criteria in another column.
gt;
gt; In one data sheet, i have:
gt;
gt; Name AHT Quality Resolution
gt; ------------------------------------------------------
gt; sean 546 90 100
gt; john 342 100 100
gt; john 369 75 98
gt; sean 786 100 100
gt; sean 540 72 76
gt; john 352 95 95
gt;
gt;
gt; In another consolidated data sheet, i have:
gt;
gt; Name AHT Quality Resolution
gt; ------------------------------------------------------
gt; Sean
gt; John
gt;
gt; what i am trying to do is find an average for john amp; Sean:
gt;
gt; =sumproduct((a:a)=a1,(b:b))/count(b:b)
gt;
gt; getting #NUM...
gt;
gt; P.S: i donot want to use fixed ranges for both the columns, hence using
gt; (a:a) amp; (b:b).
gt;
gt; I have come across a formula using Average amp; IF:
gt;
gt; =Average(IF(a2:a3500)=a1,(b2:b3500))
gt;
gt; but here i get results based on Fixed ranges, which i dont want.
gt;
gt; Have tried SumIF also, but using fixed ranges amp; the average is not
gt; correct....
gt;
gt;
gt;
gt; Can anyone help me with SumProduct.
gt;
gt;
gt; Rgds,
gt;
Try

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

HTH
On 20 May 2006 14:52:40 -0700, quot;junoonquot; gt; wrote:

gt;Hi,
gt;
gt;I am trying to use sumproduct to get an average of a column, based on a
gt;criteria in another column.
gt;
gt;In one data sheet, i have:
gt;
gt;Name AHT Quality Resolution
gt;------------------------------------------------------
gt;sean 546 90 100
gt;john 342 100 100
gt;john 369 75 98
gt;sean 786 100 100
gt;sean 540 72 76
gt;john 352 95 95
gt;
gt;
gt;In another consolidated data sheet, i have:
gt;
gt;Name AHT Quality Resolution
gt;------------------------------------------------------
gt;Sean
gt;John
gt;
gt;what i am trying to do is find an average for john amp; Sean:
gt;
gt;=sumproduct((a:a)=a1,(b:b))/count(b:b)
gt;
gt;getting #NUM...
gt;
gt;P.S: i donot want to use fixed ranges for both the columns, hence using
gt;(a:a) amp; (b:b).
gt;
gt;I have come across a formula using Average amp; IF:
gt;
gt;=Average(IF(a2:a3500)=a1,(b2:b3500))
gt;
gt;but here i get results based on Fixed ranges, which i dont want.
gt;
gt;Have tried SumIF also, but using fixed ranges amp; the average is not
gt;correct....
gt;
gt;
gt;
gt;Can anyone help me with SumProduct.
gt;
gt;
gt;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 into a list by
means of Data|List|Create List and invoke using current ranges:

=AVERAGE(IF($A$2:$A$400=quot;seanquot;,IF($B$2:$B$400 gt; 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)=quot;seanquot;,IF ($B$2:INDEX($B$2:$B$65536,X2)
gt; 0,$B$2:INDEX($B$2:$B$65536,X2))))

Again, completed with control shift enter.

junoon wrote:
gt; Hi Richard,
gt;
gt; One small problem!
gt;
gt; If in column B or C or D there are 0 values like:
gt;
gt;
gt; Name AHT Quality Resolution
gt; ------------------------------------------------------
gt; sean 546 0 100
gt; john 342 100 0
gt; john 369 75 98
gt; sean 786 100 0
gt; sean 540 0 76
gt; john 0 95 95
gt;
gt; Then, How do we get a correct Average?
gt;