Question: I'm using Google Sheets to collect data of daily arrivals of products. My data looks as shown below:

[![Data](https://i.stack.imgur.com/V2CJZ.png)](https://i.stack.imgur.com/V2CJZ.png)

What I'm trying to achieve is a sum by product:

[![Target](https://i.stack.imgur.com/bAhIp.png)](https://i.stack.imgur.com/bAhIp.png)

I've tried something with a Script but with not much luck because I'm a little lost. Is there a way to do this through formulas? Or would it be easier using Scripts/Macros?

Thanks for any help you may provide!

Answer: 
This can be done with native google sheet formula's. Change ALL the A1:D3 to your range. Leave out the headers (row 1) and the ID (column A). So start at B2. I don't understand the ID column. Does not seem to do something?

The formula:

```
=QUERY({
  FILTER(FLATTEN(A1:D3),ISTEXT(FLATTEN(A1:D3))),
   FILTER(FLATTEN(A1:D3),ISNUMBER(FLATTEN(A1:D3)))},
     "SELECT Col1, SUM(Col2) GROUP BY Col1 LABEL Col1 'Products', SUM(Col2) 'KG'",0)

```

So FLATTEN will create a list like this:

1. Snack
2. 4
3. General
4. 6

We filter out all the text and all the number, then this is the input data for the query. TIP: Copy the formula's out the main formula and see what it does.
If I'm understanding your post correctly, you have an ID column followed by 40 columns which alternate "product ... kg ... product ... kg ..." for a total of 41 columns running from A to AO.

I don't know the name of your source-data sheet. So I'll just call it "Source Sheet" in my formula. Change that throughout to your actual source-data sheet name.

You will want this formula in a *separate sheet*: not the source-data sheet:

`=ArrayFormula({"ID","PROD","KG";QUERY(SPLIT(FLATTEN(FILTER('Source Sheet'!A2:A,'Source Sheet'!A2:A<>"")&"|"&FILTER('Source Sheet'!B2:AO,ISEVEN(COLUMN('Source Sheet'!B2:AO2)))&"|"&FILTER('Source Sheet'!B2:AO,ISODD(COLUMN('Source Sheet'!B2:AO2)))),"|",0,0),"Select * Where Col2 Is Not Null")})`

Before I invest time explaining how it works, please test it out and report back whether this formula produces the desired result. If so, I'll explain the workings of it.