I have the following table1 with multiple rows:

| Element | Price | Output |

Then I have another table2:

| Quantity | Cost |

What I want for the Cost column is to get ROUNDUP(table2.Quantity/table1.Output)*table1.Price of each row of table1 and then get the SUM of each of the results. In other words:

ROUNDUP(table2.Quantity1/table1.Output1)*table1.Price1 +
ROUNDUP(table2.Quantity1/table1.Output2)*table1.Price2 +
ROUNDUP(table2.Quantity1/table1.Output3)*table1.Price3 + ...

This answer assumes that table2 has the same elements as table 1 and in the same order. It does not use Excel tables but ordinary ranges. It uses an array formula which is created with Ctrl-Shift-Enter. Consider the two ranges below:

table1 table2

In some cell, e.g., K1, create the formula


but while in that cell press Ctrl-Shift-Enter.

The formula bar should show: enter image description here

and cell K1 should show 58.

Using Excel tables, the formula looks like this: enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.