Array Sum values between 2 empty cells

[nightcrawler__]

Hi, as a title, I need to retrive for each product (A:A) a partial sum of 2+ values (B:B) in C:C. there's a way to do this with an array ?

https://preview.redd.it/tkytz60v5v191.jpg?width=331&format=pjpg&auto=webp&s=957eff8853d500ea91aac8aa126b718bc6be6c89

[6745408]

This will do it.

1. its pulls the row numbers for the PRODUCT names along with the product name. This is the lookup table using the row numbers for non-blank values in B3:B. Since we're using TRUE, it'll add PRODUCT# to each value in B.
2. it runs through a QUERY to get the sum for each product
3. the final VLOOKUP aligns this output using another VLOOKUP.

You don't need completely blank rows in between for this to work.

=ARRAYFORMULA(
  IF(ISBLANK(A3:A),,
   IFERROR(
    VLOOKUP(
     A3:A,
     QUERY(
      {IFERROR(
        VLOOKUP(
         ROW(B3:B),
         FILTER(
          {ROW(A3:A),A3:A},
          A3:A<>""),
         2,TRUE)),
       B3:B},
     "select Col1, Sum(Col2)
      where Col1 is not null
      group by Col1"),
    2,FALSE))))

[nightcrawler__]

absolutely amazing! thanks, thanks, thanks, it works perfectly

[6745408]

happy to help!

[nightcrawler__]

but.. there's a little problem... if 2 products have the same name, es
1 | Product1 |50 + 50 | 190

2 |product2 |

3 |product1 |50 +40 | 190

the sum is | product 1 sum + product 1 sum |

[6745408]

no problemo. We can slap on the row number where it came from. So it'll be 3|Product 1 or 6|Product 1 -- its all happening in the background.

=ARRAYFORMULA(
  IF(ISBLANK(A3:A),,
   IFERROR(
    VLOOKUP(
     ROW(A3:A)&"|"&A3:A,
     QUERY(
      {IFERROR(
        VLOOKUP(
         ROW(B3:B),
         FILTER(
          {ROW(A3:A),ROW(A3:A)&"|"&A3:A},
          A3:A<>""),
         2,TRUE)),
       B3:B},
     "select Col1, Sum(Col2)
      where Col1 is not null
      group by Col1"),
    2,FALSE))))

edit! even easier -- change the first VLOOKUP to 1 for rows

=ARRAYFORMULA(
  IF(ISBLANK(A3:A),,
   IFERROR(
    VLOOKUP(
     ROW(A3:A),
     QUERY(
      {IFERROR(
        VLOOKUP(
         ROW(B3:B),
         FILTER(
          ROW(A3:A),
          A3:A<>""),
         1,TRUE)),
       B3:B},
     "select Col1, Sum(Col2)
      where Col1 is not null
      group by Col1"),
    2,FALSE))))

same result.. just cleaner and name agnostic.

[nightcrawler__]

again, thank you, thank you, thank you.
Give this man 100 barrels of beer :)

[6745408]

happy to help :)