Sumproduct with array criteria

[not very bright dave]

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

 

[Krishnakumar]

Hi,

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

HTH

 

[DominicB]

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

 

[pgc01]

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

 

[barry houdini]

One way.....

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