6

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#

3

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 |    |
+---+-------+---+---+----+----+
3

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 |
+---+--------+----+----+
0

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

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.