# combine 2 sumproduct formula's

#### JWARD

##### Board Regular
the following two formula's, how do i combine them into one?

=SUMPRODUCT((\$A\$15:\$A\$100="TRI-CITY")*(\$J\$15:\$J\$100="1"))
=SUMPRODUCT((\$B\$15:\$B\$100="TRI-CITY")*(\$L\$15:\$L\$100="1"))

thank you

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### Joe4

=SUMPRODUCT((\$A\$15:\$A\$100="TRI-CITY")*(\$J\$15:\$J\$100="1")*(\$B\$15:\$B\$100="TRI-CITY")*(\$L\$15:\$L\$100="1"))

#### JWARD

##### Board Regular
got it, this worked

=SUMPRODUCT((\$A\$15:\$A\$100="TRI-CITY")*(\$J\$15:\$J\$100="1")+(\$B\$15:\$B\$100="TRI-CITY")*(\$L\$15:\$L\$100="1"))

#### Oaktree

##### MrExcel MVP
Depends on what you mean. jmiskey's formula assumes you mean ALL of the 4 criteria must be met.

If you mean if EITHER pair is met, then try something like:

=SUMPRODUCT(--ISNUMBER(SEARCH("Tri-City",\$A\$15:\$A\$100&"|"&\$B\$15:\$B\$100)),--ISNUMBER(SEARCH("|1|","|"&\$J\$15:\$J\$100&"|"&\$L\$15:\$L\$100&"|")))

Replies
2
Views
76
Replies
3
Views
167
Replies
6
Views
45
Replies
6
Views
75
Replies
4
Views
76

1,127,595
Messages
5,625,697
Members
416,128
Latest member
WarJamAnd

### 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