# Sumproduct with array criteria

#### not very bright dave

##### Board Regular
Hi guys

Does anyone know if it's possible to run a sumproduct on a criteria range (effectively an 'and')?

E.g. SUMPRODUCT((W13:W22=Z13:Z15)*(X13:X22))

Cheers

Dave

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi,

=SUMPRODUCT(SUMIF(W13:W22,Z13:Z15,X13:X22))

HTH

Good morning not very bright dave

If I understand you correctly then, no I don't think you can. However, you can use a combination of Sum and If to produce an array formula that should work OK for you :

=SUM(IF(W13:W22=Z13:Z15,X13:X22,0))

Commit using Ctrl + Shift + Enter.

HTH

DominicB

Hi Dave

Yes you can but the ranges must be orthogonal:

=SUMPRODUCT((W13:W22=TRANSPOSE(Z13:Z15))*(X13:X22))
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

Hope this helps
PGC

One way.....

=SUMPRODUCT(--ISNUMBER(MATCH(W13:W22,Z13:Z15,0)),X13:X22)

Replies
13
Views
534
Replies
3
Views
318
Replies
3
Views
195
Replies
0
Views
307
Replies
22
Views
767

1,221,501
Messages
6,160,177
Members
451,629
Latest member
MNexcelguy19

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