J
junoon
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,
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,