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

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