using the OR function in arrays

[BobDaBuilder]

I'd like to total the number of UNITS in an array if the customer is Steve or Sally. I've tried the following

{=SUM(IF((product="car")*(color="red")*OR(customer="Steve",customer="Sally"),UNITS,0))}

the function

{=SUM(IF((product="car")*(color="red")*(customer="Steve"),UNITS,0))}

gives me the number purchased by Steve. I'd like the number purchased by either Steve or Sally.

I thought the OR might work, but it apparently gets evaluated as TRUE and everything in the UNITS column gets summed...

Any suggestions? Thanks!

[Portuga]

Sum both values:

Please Login or Register  to view this content.

[daddylonglegs]

OR function only returns a single result not an array of results to match your array/range. I'd use SUMPRODUCT to avoid CTRL+SHIFT+ENTER, i.e.

=SUMPRODUCT((product="car")*(color="red")*(( customer ="Steve")+(customer="Sally")),UNITS)

[BobDaBuilder]

OR function only returns a single result not an array of results to match your array/range. I'd use SUMPRODUCT to avoid CTRL+SHIFT+ENTER, i.e.

=SUMPRODUCT((product="car")*(color="red")*(( customer ="Steve")+(customer="Sally")),UNITS)

Wow. I had not seen SUMPRODUCT used this way before. Thanks!