Average nesting within Sumproduct?

[Guest]

I'm bamboozled!...and would welcome assistance from someone with greater
understanding of nesting functions than I. I've got the first bit, but can't
work out the next part:
Part A: If the value in cell M24 is found in range A2:a500, and if the item
code in range J2:J500 commences with "A", then sum the $values in range
I2:I500
Part B: Return an average based on the values in range I2:I500 (which meet
the above criteria) multiplied by the value in P24.

 

[T. Valko]

Try this array formula** :

=AVERAGE(IF(A2:A500=M24,IF(LEFT(J2:J500)="A",IF(ISNUMBER(I2:I500),I2:I500))))*P24

 

[Guest]

thanks for your response. Working on your formula, I've done this in Q25
=AVERAGE(IF(A2:A1167=M24,IF(LEFT(K2:K1167)="c",IF(ISNUMBER(J2:J1167),J2:J1167))))*Q24
which has returned the total $value of the occurences, rather than the
average of the total $value. In Q26, underneath, I've done this:
=AVERAGE(Q25)/Q24
Question: It's possible K2:K1167 contains more than item code which should
be collected. If I adjust the "c" to display "c","n", it returns a zero
$value.

 

[T. Valko]

Take a look at this screencap:

http://img159.imageshack.us/img159/3819/averageifek6.jpg

I have no idea what your actual data is but the formula works as shown in
that screencap. Did you enter the formula as an array? I see in my other
reply I forgot to include my array formula reminder:

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)