SUM, IF, OR, AND array

[bob]

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

 

[T. Valko]

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=$B2)>0),Chart1!$AV$2:$AV$7500)

 

[xlmate]

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]

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

 

[Bernard Liengme]

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