If $J2:$J20000 = "to" and $L2:$L20000 or $N2:$N20000 = $B2, then I want to
SUM $AV2:$AV20000

Can anyone kindly help me with an array formula for this? Thank you.

Bob

Try this. Normally entered:

=SUMPRODUCT(--($J2:$J20000="to"),--(($L2:$L20000=$B2)+($N2:$N20000=$B2)0),$AV2:$AV20 000)


--
Biff
Microsoft Excel MVP


"bob" wrote in message
...
If $J2:$J20000 = "to" and $L2:$L20000 or $N2:$N20000 = $B2, then I want
to
SUM $AV2:$AV20000

Can anyone kindly help me with an array formula for this? Thank you.

Bob

Something like this, normal ENTER:
=SUMPRODUCT((J2:J10="to")*((L2:L10=B2)+(N2:N10=B2) ),AV2:AV10)
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"bob" wrote:
If $J2:$J20000 = "to" and $L2:$L20000 or $N2:$N20000 = $B2, then I want to
SUM $AV2:$AV20000

Hi Max,

I believe your solution will double-count entries if say column J2 = to and
column N2 = B2.

Cheers,
Shane Devenshire

"Max" wrote:

Something like this, normal ENTER:
=SUMPRODUCT((J2:J10="to")*((L2:L10=B2)+(N2:N10=B2) ),AV2:AV10)
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"bob" wrote:
If $J2:$J20000 = "to" and $L2:$L20000 or $N2:$N20000 = $B2, then I want to
SUM $AV2:$AV20000

I believe your solution will double-count entries
if say column J2 = to and column N2 = B2.

Correct, Shane. Thanks.

It should have been:
=SUMPRODUCT((J2:J10="to")*((L2:L10=B2)+(N2:N10=B2) 0),AV2:AV10)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---