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

Hi Eric

Try this, in one formula line (has not been tested):

=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),--(crq!$Z$2:$Z$65536<=650),crq!$J$2:$J$65536)/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),--(crq!$Z$2:$Z$65536<=650)

Hopes this helps.

---
Per


"EricB" skrev i meddelelsen
...
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

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
---
"EricB" wrote:
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

Typo alert!

The formula miss a closing paranthesis in last SumProduct statement.

//Per

"Per Jessen" skrev i meddelelsen
...
Hi Eric

Try this, in one formula line (has not been tested):

=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),--(crq!$Z$2:$Z$65536<=650),crq!$J$2:$J$65536)/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),--(crq!$Z$2:$Z$65536<=650)

Hopes this helps.

---
Per


"EricB" skrev i meddelelsen
...
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

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

ctrl+shift+enter, not just enter


"EricB" wrote:

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