SUMPRODUCT with 3 Arrays - What am i doing wrong?

[skinfreak]

=SUMPRODUCT(($M$5:$M$434=$M$5)*($S$5:$S$434=$S$14)*($S$5:$S$434=$S$190))

To clarify:

Column M has a specific value at cell M5 that I want to search for. The same can be said for the contents of cells S14 and S190. But when I run the above forumula, I get an answer of 0. If I remove the end argument, i get 74. If I remove themiddle argument, I get 14. Can I just tack on extra arrays to a sumproduct? I want a result that diplays the number or rows with either M5 & S14 or M5 and S190. Or, should I just run two sumproducts. I think i need an OR in there somewhere?

Thanks!

 

[Greg Truby]

Try:

=SUMPRODUCT(($M$5:$M$434=$M$5)*(($S$5:$S$434=$S$14)+($S$5:$S$434=$S$190)))

 

[skinfreak]

That simple! Thanks very much!

 

[Aladin Akyurek]

That simple! Thanks very much!

It's a bit strange that the formula directly refers to cells in the ranges for which they must hold.

 

[skinfreak]

Can you use wildcard text strings with this formula? eg. "*"&"textstring"&"*"??

 

[Aladin Akyurek]

Can you use wildcard text strings with this formula? eg. "*"&"textstring"&"*"??

Put the condition string (without the wildcards around) in a cell of its own, say, X2 and add a term/conditional to the SumProduct formula:

=SUMPRODUCT(--ISNUMBER(SEARCH(X2,RangeOfInterest)),...)