Search code examples
excel-formulasumproduct

SumProduct with multiple AND and OR criteria

I can't work out how to structure this formula so will try to explain a basic example.

Where cells in column A=1 & B=2 & C=3
I then want to keep these conditions but also include either
columns X&Y=26 (X=26 AND Y=26) OR column Z=26

When I put this into formula format, I am currently trying (incorrectly):

` =SUMPRODUCT(--(A1:A10=1),--(B1:B10=2),--(C1:C10=3),AND(--(X1:X10=26),--(Y1:Y10=26))+--(Z1:Z10=26)*ROW(M1:M10))

`

Can someone please enlighten me as to how this should be tackled?

Solution

  • Try the following formula...

    =SUMPRODUCT(--(A1:A10=1),--(B1:B10=2),--(C1:C10=3),--((X1:X10=26)*(Y1:Y10=26)+(Z1:Z10=26)>0)*ROW(M1:M10))