Sumproduct with OR statement

Hi folks,

I'm trying to get a SUMPRODUCT formula that will use an OR statement in it. What I need to do is sum all the order values where one staff member is involved in @ least one step of the order process.


zyj2pz.jpg

So basically, as you can see, Daniel is involved in 3 transactions, but one of them (Oct 7th) he's done both steps. I need to sum all the orders he's involved in but don't want to double count the 7th's order.

the formula I have so far is this: =SUMPRODUCT((D2:D9)*((C2:C9=A13)+(B2:B9=A13)))

but this obviously double counts on that date.

Can anyone please help with ammending the formula to suit my needs?

Thanks

Try this version

=SUMPRODUCT(D2:D9,((C2:C9=A13)+(B2:B9=A13)>0)+0)

wow that was quick!


edit: I sort of understand the other change you made but could you please tell me why you're adding a +0 at the end?


Thanks spiderman

i'm a little late w/ this reply, but i just found this post useful a few minutes ago.

the +0 turns an array of true/false into 1/0. could also be done w/ "--" before the parenthesis