Sumproduct question

[ASpannTx]

Thanks to your wonderful site I was able to solve half my formula problem

I have the following formula:

=SUMPRODUCT((C4:C304=701)*(L4:L304>=0.01))

But I need it to also look at M4:M304 and N4:N304 and if L,M OR N are >=0.01 count but sometime there might be a value in L M and N but i only want it to count once for that row.

Thanks in advance for any help.

 

[Dave Patton]

Welcome to the forum

try

=SUMPRODUCT(--((L4:L304>=0.01)+(M4:M304>=0.01)+(N4:N304>=0.01)>0)*(C4:C304=701))

 

[ASpannTx]

That did exactly what I wanted it to do, perfect... Thanks a million!

 

[Dave Patton]

Thanks for the feedback.

 

[ASpannTx]

Most definitely, I am so excited that it works... although I really don't understand the why behind it... if you don't mind, can you explain in layman terms the logic? If not, I am okay with Just the fact that it works.

 

[Dave Patton]

We can evaluate a formula with Tools | Formula Auditing | Evaluate (Alt tuf) or use the icon (Evaluate Formula). You may have to add the the evaluate icon to your menu of icons.

With the above, we can visually step through the calculation. It is a good idea to work with a very concise example so we can focus on the particular challenge; it also makes it easier to follow the "Evaluation".

This helps us understand the formula and/or to troubleshoot it.

You can also search the forum for many examples and explanations.

Mr. Aladin Akyurek in particular plus others contributed greatly to the knowledge base re SumProduct.

 

[ASpannTx]

Thanks again, you saved me alot of time fighting with it... Many, many thanks

 

[shemayisroel]

ASpannTx,

See below links to some really good white papers on the function.

http://www.mrexcel.com/forum/showthread.php?p=1824821

 

[ASpannTx]

Thanks for the additional information, it helped me understand the function a little better. Thanks again to both of you for your assistance. It is much appreciated.

 

[shemayisroel]

Thanks for the additional information, it helped me understand the function a little better. Thanks again to both of you for your assistance. It is much appreciated.

No probs, your welcome.