Using the following examples:
Main sheet:
+----------+-----+
| A | B |
+----------+-----+
| Value1 | 3 |
| Value2 | 0 |
| Value3 | 1 |
| Value4 | 2 |
+----------+-----+
RangeA: (Column A is identical to that of the main sheet)
+----------+-------+---------+
| A | B | C |
+----------+-------+---------+
| Value1 | ... | Type1 |
| Value2 | ... | Type2 |
+----------+-------+---------+
RangeB:
+---------+-------+--------+
| Type1 | 5 | 20 |
| Type2 | 20 | 100 |
| Type3 | 100 | 400 |
| Type4 | 400 | 1600 |
+---------+-------+--------+
I have a formula that returns an integer value for each row on the main sheet:
=MAX(0, B - 2) * VLOOKUP(VLOOKUP(A, RangeA, 3, FALSE), RangeB, 2)
So if run on the first row, the value of 5 is returned. On the second row, 0, and so on.
What I would like to do is get the sum of this formula run on all rows, in a single cell. I've attempted various methods using ARRAYFORMULA but have had no luck.
I would appreciate any help.
