SUM, IF, OR, AND array
 

I am seeking a formula for the following:

On 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 add all corresponding numbers in Chart1!$AV$2:$AV$7500.

thanks in advance,
Bob

SUM, IF, OR, AND array
 

Try this:

=SUMPRODUCT(--(Chart1!$B$2:$B$7500=$C2),--(Chart1!$C$2:$C$7500=$A2),--((Chart1!$L$2:$L$7500=$B2)+(Chart1!$N$2:$N$7500=$B 2)0),Chart1!$AV$2:$AV$7500)


--
Biff
Microsoft Excel MVP


"bob" wrote in message
...
I am seeking a formula for the following:

On 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 add all corresponding numbers in Chart1!$AV$2:$AV$7500.

thanks in advance,
Bob

SUM, IF, OR, AND array
 

It is not clear on what you want to do, for instance
what is in Chart1!$L$2:$L$7500 or Chart1!$N$2:$N$7500 that you want to look
and based on that
add to the total?

post a sample and your the result you are seeking for better undertanding

--
HTH

Pls provide your feedback by clicking the YES button below if this posting
is helpful
This will help others to search the results in the archive better

cheers, francis
"bob" wrote in message
...
I am seeking a formula for the following:

On 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 add all corresponding numbers in Chart1!$AV$2:$AV$7500.

thanks in advance,
Bob

SUM, IF, OR, AND array
 

this seems to work...thank you very much. can you please tell me the function
of the two dashes (--). thanks.

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(Chart1!$B$2:$B$7500=$C2),--(Chart1!$C$2:$C$7500=$A2),--((Chart1!$L$2:$L$7500=$B2)+(Chart1!$N$2:$N$7500=$B 2)0),Chart1!$AV$2:$AV$7500)


--
Biff
Microsoft Excel MVP


"bob" wrote in message
...
I am seeking a formula for the following:

On 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 add all corresponding numbers in Chart1!$AV$2:$AV$7500.

thanks in advance,
Bob

SUM, IF, OR, AND array
 

Double negation coerces Boolean FALSE/TRUE to 0/1

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"bob" wrote in message
...
this seems to work...thank you very much. can you please tell me the
function
of the two dashes (--). thanks.

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(Chart1!$B$2:$B$7500=$C2),--(Chart1!$C$2:$C$7500=$A2),--((Chart1!$L$2:$L$7500=$B2)+(Chart1!$N$2:$N$7500=$B 2)0),Chart1!$AV$2:$AV$7500)


--
Biff
Microsoft Excel MVP


"bob" wrote in message
...
I am seeking a formula for the following:

On 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 add all corresponding numbers in Chart1!$AV$2:$AV$7500.

thanks in advance,
Bob