Can't get correct sums from data in a sheet either by sumif, sumifs, sumproduct or query-CodePudding

I have a sheet where I entered all products that I have bought in year 2021. This is on Sheet1.

On Sheet2 I want to get listed unique products (that I have managed to do) and how many of each unique product I have bought inside the country (home) and how many I bought outside the country (abroad) and how many I bought together outside and inside my country.

I got some numbers as a results of trying with different formulas but then I wanted to see if the results are correct and everything stopped :(

basically should products bought abroad plus products bought home equal to total product bought.

At the end I decided to use the query formula to achieve my goal because it is most easy to understand due to the similarity of its select statement to english language.

So, I composed these 3 formulas:

=QUERY(Sheet1!A2:N,"SELECT B, SUM(G) WHERE L Matches 'Metro|Farmah|Mulackal|Interspar Graz' group by B LABEL SUM(G) 'ABROAD'",0)

=QUERY(Sheet1!A2:N,"SELECT SUM(G) WHERE NOT L Matches 'Metro|Farmah|Mulackal|Interspar Graz' group by B LABEL SUM(G) 'HOME'",0)

=QUERY(Sheet1!A1:N,"SELECT SUM(G) group by B LABEL SUM(G) 'TOTAL'",0)

But my problem is that abroad home doesn't give total :(

I also had problems omitting title row of query formula results what I also haven't succeeded or it was reacting very whimsically by adding a blank row before results of query etc... But this is not the main urgent think.

The main urgent thing is to get correct result of calculations on which I can really that are absolutely correct amounts equal to what I get by manual check for random product from the sheet.

Any idea please?

I don't mind if others formulas are used instead of query like for instance sumif or arrayformula(sumif or sumproduct or whatever as long as the thing works as expected.

This is the link of the spreadsheet:

CodePudding user response：

If you don't want to see zero values, you can use the following formula (preferably in cell A1 of a new sheet):

=ArrayFormula(QUERY({Sheet1!B:B, Sheet1!G:G, IF(REGEXMATCH(Sheet1!L:L, "Metro|Farmah|Mulackal|Interspar Graz"), "ABROAD", "HOME"); Sheet1!B:B, Sheet1!G:G, IF(ROW(Sheet1!A:A), "TOTAL")}, "Select Col1, SUM(Col2) WHERE Col1 Is Not Null GROUP BY Col1 PIVOT Col3"))