Sumif + groupby with dates

[kdev]

What I have

2 sheets: stats and data

In data sheet I have a table:

code | amount | date

xxx | 1.00 € | 10/05/2021 16:37:23

yyy | 3.00 € | 01/06/2021 16:37:23

xxx | 2.00 € | 10/06/2021 16:37:23

yyy | 3.00 € | 10/06/2021 16:37:23

Let’s assume that each row is a transaction.
For the calculations I noticed that the format of the col date might be harder but if it’s not possible or it’s too hard, I can split it in two cols: one for the date and one for the hour.

What I want

In stats sheet I want to have tables with:

1. Total amount (sum of col amount of sheet data)
2. Total amount by week (sum of col amount of sheet data grouped by date col)
3. Total amount by month (sum of col amount of sheet data grouped by date col)
4. Total transactions by week
5. Total transactions by month
6. Total amount grouped by code (sum of col amount of sheet data grouped by col code)
7. Total amount grouped by code and by month (sum of col amount of sheet data grouped by col code)
8. Total transactions grouped by code and by month

In this example I would end up with:

1. Total: 6 €
2. Week of MAY with a total of 1 €. Week 1 of JUNE with a total of 3€ and week 2 with a total of 5€.
3. Month of MAY with a total of 1 €. Month of JUNE with a total of 8.
4. Week of MAY with a total of 1 . Week 1 of JUNE with a total of 1 and week 2 with a total of 2.
5. Month of MAY with a total of 1 . Month of JUNE with a total of 3.
6. Code xxx with a total of 3 €. Code yyy with a total of 6€.
7. For MAY we have the code xxx with a total of 1 €. For JUNE we have xxx with a total of 2€ and yyy with a total of 6€.
8. For MAY we have xxx with 1 . For JUNE we have xxx with 1 and yyy with 2.

I tried to follow the accepted answer of this other question that uses the SUMPRODUCT() but it’s giving me error.

Pls find the document prepared attached.demo.ods

[Lupp]

What you want to achieve isn’t well done by spreadsheets - except in very specific cases with a definitely assured low emergence of data, and low requirements concerning reliability, safety, and maintainability (scaling and enhancements).
Cutting the real sequence of days in isolated years. e.g, will basically conflict with the lunisolar calendar we have - and actually you want to group by months and weeks as well. They aren’t compatible,
Not splitting your data in years, they may soon be too many to be efficiently handled by sheets. Lots of thinlkable complications.
One day you will need to tranfer everything to a database.

(A “stats” sheet like you are dreaming of can only work if cutting into yeras is “hard coded”. Forget the weeks in this case. Grouping by code will include grouping by year without mention…
How shall your filters co-operate with the formulas?)

If you think your task can be assured to stay small enough, use pivot tables.

[mikekaganski]

I’d simply use pivot tables with grouping.

[Lupp]

Well, for one year? What about the weeks?

[mikekaganski]

Weeks are by 7 days…

[kdev]

@Lupp I also thought about using SQLite because it would be easier to retrieve the data but excel wins in terms of the input of the data.
This will be for a small amount of data, so it can be handled by the spreadsheet.

[Lupp]

I’m not atually a user of any self-made database, and when I still was forced to use “ready-made” databases for very specific tasks, I experienced lots of problems leading me to “cross-import-export-tricks” and preparing spreadsheets for the support of my creative part. But that’s another strand.
Concerning your input issue:
I think (a) well designed spreadsheet(s) should be usable as (a) database table(s),. or as the source for an input to a database. If the problem was mine, I would probably study
https://forum.openoffice.org/en/forum/search.php?keywords=sheet++%2Bdatabase++%2Btable++
or explicitly ask here for how to do it.
(The answer wouldn’t come from @Lupp .)

[PKG]

Total amount by week
►=SUMPRODUCT(WEEKNUM(date,21)=C3,amount)

Total amount by month

►=SUMPRODUCT(MONTH(date)=MONTH(F3),amount)

Total transactions by week

► =SUMPRODUCT(IF(date="","",WEEKNUM(date,21)=I3))

Total transactions by month

►=SUMPRODUCT(IF(date="","",MONTH(date)=MONTH(L3)))

Total amount grouped by code

►=SUMPRODUCT(code=O3,amount)

Total amount grouped by code and by month
►=SUMPRODUCT(MONTH(date)=MONTH(S$2),code=$R3,amount)

Total transactions grouped by code and by month

► =SUMPRODUCT(MONTH(date)=MONTH(AA$2),code=$R3)

[kdev]

I’m going to test it, and retrieve the results

[PKG]

Transaction.ods

[kdev]

You rock! How can I thank you? Do you have a lightning wallet to send you some Bitcoin? Or something else?

Big thanks