# Sum of values under a merged Cell

I would like to get the Sum for the following formatted spreadsheet:

``````|PIZZA |ABC |21 |
|      |GHT |12 |
|      |ERT |45 |
|      |DFT |2  |=21+12+45+2
|EGGS  |GTD |96 |
|      |YHJ |5  |
|      |PHJ |78 |=96+5+78
``````

The list continues for 1000+ items with variable amount of LOT#

This solution uses a helper column D (it could be hidden, or placed far to the right on the sheet).

Formula in D2: `=if(len(A2), C2, C2+D1)`, then dragged down the row. This computes a cumulative sum, restarting every time there is an entry in A column.

Formula in E2: `=if(len(A3), D2,)`, then dragged down the row. This only hides the intermediate results of the calculation of the cumulative sums.

``````+---+-------+---+---+----+----+
|   |   A   | B | C | D  | E  |
+---+-------+---+---+----+----+
| 1 |       |   |   |    |    |
| 2 | pizza | q | 2 |  2 |    |
| 3 |       | w | 3 |  5 |    |
| 4 |       | e | 4 |  9 |    |
| 5 |       | r | 5 | 14 | 14 |
| 6 | eggs  | d | 6 |  6 |    |
| 7 |       | f | 7 | 13 |    |
| 8 |       | g | 8 | 21 | 21 |
| 9 | ham   | h | 1 |  1 |    |
+---+-------+---+---+----+----+
``````

This solution uses an array formula and named ranges:

Formula

```=ArrayFormula(
IFERROR(
POWER(
POWER(
MMULT(
TRANSPOSE(
N(
vlookup(ROW(Categories),FILTER(ROW(Categories),LEN(Categories)),1)
=
TRANSPOSE(
vlookup(ROW(Categories),FILTER(ROW(Categories),LEN(Categories)),1)
)
)
*
Values
),
SIGN(ROW(Categories))
)
*
N({LEN(Offset(Categories,1,,ROWS(Categories)-1))>=1;1}),
-1),
-1)
)
)
```

Example

Set Categories as the name of A1:A7 and Values as the name of B2:B7. Then add the above formula to C1. The result will look like the following:

```+---+--------+----+----+
|   |   A    | B  | C  |
+---+--------+----+----+
| 1 | orange | 20 |    |
| 2 |        |  7 |    |
| 3 |        | 20 | 47 |
| 4 | apple  | 13 |    |
| 5 |        |  2 | 15 |
| 6 | banana | 17 |    |
| 7 |        | 16 | 33 |
+---+--------+----+----+
```

Please try also this 2 formulas:

# 1

```=ArrayFormula(TRANSPOSE( SPLIT( JOIN("", ArrayFormula( REPT( FILTER(Categories,Categories<>"")&"-", len(TRANSPOSE(SPLIT(REGEXREPLACE(JOIN(",",Categories)&",","[A-Za-z]+","-"),"-"))) ) ) ),"-" ) ))```

# 2

```=ArrayFormula(VLOOKUP(ROW(Categories), QUERY({ROW(Categories),Categories},"select * where Col2 <> ''"), 2))```

# Profit

1. They both use original array only 3 times.
2. ArrayFormulas
3. Second is shorter