SUMPRODUCT with AVERAGE result

[EricB]

SUMPRODUCT with AVERAGE result

Formula refers:
=SUMPRODUCT(--(crq!$C$2:$C$65536="Debit
Order"),--(crq!$D$2:$D$65536="FNB"),--(crq!$H$2:$H$65536="APP"),--(crq!$Z$2:$Z$65536>=550<=650),crq!$J$2:$J$65536)

1) What criteria do I use to pickup records being equal to and greater than
550 but not exceeding 650?
2) I need to get the AVERAGE of the result in Column J, what do I do to get
an AVERAGE?

At present my result is 0

Regards

EricB

 

[Max]

For Q1 ..
Try change this bit:

.. ,--(crq!$Z$2:$Z$65536>=550<=650),

to this (split it up into 2 separate criteria terms):
... ,--(crq!$Z$2:$Z$65536>=550),--(crq!$Z$2:$Z$65536<=650),


For Q2 ..
you could try an array-entered** conditional AVERAGE(IF .. ,
indicatively something like this:
=AVERAGE(IF(Cond1*Cond2*Cond3*Cond4,Range_J))
where Cond1, Cond2, Cond3 etc would be your criteria eg:
(crq!$C$2:$C$65536="Debit Order")
(crq!$D$2:$D$65536="FNB")
(crq!$H$2:$H$65536="APP")
etc

**press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---

 

[Per Jessen]

Typo alert!

The formula miss a closing paranthesis in last SumProduct statement.

//Per

 

[Teethless mama]

=AVERAGE(IF((rngC="Debit
Order")*(rngD="FNB")*(rngH="APP")*(rngZ>=550)*(rngZ<=650),rngJ))

ctrl+shift+enter, not just enter