Question: In the table below, I want to sum all of New York ACT in Q2 after the sales have been multiplied by the corresponding rates. Is there a one-shot formula I can use without needing to multiply all the items of the first table with the corresponding rates, then summing? The result I'm looking for would be (49\*.45)+(10\*.37)=25.45.

![table image here](https://i.stack.imgur.com/jTXHL.png)

Answer: 
You can insert a new column G in the table for **Rates** and write the corresponding rate in it (at the same row of store A write 0.45, B write 0.37 ...)
The result will be :  

**=SUMPRODUCT((A3:A8="New York")\*E3:E8\*G3:G8)**  

Sumproduct will multiply each cell in column E (Q2) by the rate in G if the Territory (column A) is "New York"
You could use this formula:

```
=SUMPRODUCT(($E$3:$E$8)*(LOOKUP($B$3:$B$8,$H$3:$H$8,$I$3:$I$8)))

```

[![enter image description here](https://i.stack.imgur.com/WWknt.png)](https://i.stack.imgur.com/WWknt.png)

The order in the territories and companies do not matter, but the lookup range must be in ascending order in the lookup column in this instance Column H needs to be in ascending order.