SUMPRODUCT WITH AN "OR" MAYBE????

[Guest]

I received some great help on my last question and know need to make it a
little more complex if you will.

Current Formula is:

=SUMPRODUCT((Sheet1!A2:A4=3)*(Sheet!B2:B4=100)*(Sheet!C2:C4="D")*Sheet1!D24)

Now I would like it to do:

=SUMPRODUCT((Sheet!A2:A4=3 OR A OR 4 OR B OR 999999) and then continue on.

Is it possible to use an or statement in the formula or not?

 

[Bernie Deitrick]

Sean,

To get an OR operation, simply Add the fields (using a + ) instead of
multiplying (AND, using *).

HTH,
Bernie
MS Excel MVP

 

[Don Guillett]

You can use the + instead of *

Or if you wanted different values in the same range

=SUMPRODUCT((Sheet1!A2:A4={3,"A",8,12})*(Sheet!B2:B4=100)*(Sheet!C2:C4="D")*
Sheet1!D24)

 

[papou]

Hi Sean
Use the + (plus) sign as OR operator, ie:
=SUMPRODUCT((Sheet1!A2:A4=3)+(Sheet!B2:B4=100)+(Sheet!C2:C4="D")*Sheet1!D2
4)

HTH
Cordially
Pascal