# SUMPRODUCT with 3 Arrays - What am i doing wrong?

#### skinfreak

##### Board Regular
Code:
``=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!

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### Greg Truby

##### MrExcel MVP
Try:

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

#### skinfreak

##### Board Regular
That simple! Thanks very much!

##### MrExcel MVP
skinfreak said:
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

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

##### MrExcel MVP
skinfreak said:
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)),...)

Replies
1
Views
85
Replies
4
Views
198
Replies
5
Views
382
Replies
3
Views
119
Replies
9
Views
80

1,195,600
Messages
6,010,651
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back