I'm splitting my stock of apples and oranges across three customers. Price is based on mode of transport. I want to calculate revenue by customer. I'm trying to avoid intermediate calculations in other cells and would like to describe the entire customer calculation with one formula.
The array formula I'm using doesn't seem to honor the different modes in the calculations.
The formula in B11 is at the bottom.
Any suggestions? Thank you in advance.
Since you're having products and sums, I suggest using SUMPRODUCT:
=SUMPRODUCT(($A$6:$A$10=A12)*($A$2=$C$6:$C$10)*$D$6:$E$10*$B$2)+
SUMPRODUCT(($A$6:$A$10=A12)*($A$3=$C$6:$C$10)*$D$6:$E$10*$B$3)
For Kevin and then you can drag the formula down for the other users.
($A$6:$A$10=A12) validates the name.
($A$2=$C$6:$C$10) validates the mode.
$D$6:$E$10*$B$2 gets the stock.
There might be some easier way though and haven't explored all possibilities yet since I haven't got much time right now.
Here's another solution using SUMPRODUCT. Enter the following formula in cell B12 for Kevin, and then copy it down for the others.
=SUMPRODUCT(($A$6:$A$10=A12)*($D$6:$E$10)*SUMIF($A$2:$A$3,$C$6:$C$10,$B$2:$B$3))
It's similar to @Jerry's, except that it does a "lookup" of the mode via SUMIF instead of using explicit terms for each mode.
