SUM, IF, AND, OR array
 

I am seeking a formula for the following:

From the current worksheet,
IF $B2 = Chart1!$L$2:$L$7500 OR Chart1!$N$2:$N$7500
AND
IF $A2 = Chart1!$C$2:$C$7500
AND
IF $C2 = Chart1!$B$2:$B$7500
then I want to sum all of the corresponding values in Chart1!$AV$2:$AV$7500

Thanks in advance,
Bob

SUM, IF, AND, OR array
 

Hi,

This problem suggests the benefits of using range names. In this case if
you name the ranges on the chart sheet B, C_, L, N, AV then your formula
simplifies to:


=SUMPRODUCT(--(B=$C2),--(C_=$A2),--((L=$B2)+(N=$B2)0),AV)

I use C_ instead of C because C is a reserve word in Excel meaning column.

Even without shortening the formula with range name you could choose the
less safe but shorter version:

=SUMPRODUCT((B&C_=$C2&$A2)*((L=$B2)+(N=$B2)0)*AV)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"bob" wrote:

I am seeking a formula for the following:

From the current worksheet,
IF $B2 = Chart1!$L$2:$L$7500 OR Chart1!$N$2:$N$7500
AND
IF $A2 = Chart1!$C$2:$C$7500
AND
IF $C2 = Chart1!$B$2:$B$7500
then I want to sum all of the corresponding values in Chart1!$AV$2:$AV$7500

Thanks in advance,
Bob