How to lookup expense category and sum value accordingly date month?

[Lacan]

Hello Everyone,

Would like to lookup expense category and sum value accordingly date month?
To be easy put data very short and very simple.
Any question, please let me know.
As always, thank you very much.

Fórmula Principal para o Ficheiro VDF.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4
5		Data Operação	Descrição	Montante( EUR )	Category		Expenses Category	01/06/2024	01/07/2024	01/07/20242
6		01-06-2024	Miami Beach Club	-100,00	Restaurant		Provision
7		06-06-2024	Credito Habitacao Geral (166/360)	-164,66	Provision		Restaurant
8		09-07-2024	Credito Habitacao Geral (166/360)	-164,66	Provision		Shipping
9		30-07-2024	Transferência de Mangopay	20,00
10		31-07-2024	Ordenado	1 500,00
11		01-08-2024	Aegon Santander Portugal Nao Vida	-17,91
12		02-08-2024	Pagamento de conta cartão	-200,00	Shipping
13		03-08-2024	Hyde Vegas	-50,00	Restaurant
14		04-08-2024	Pagamento de conta cartão	-200,00
15		05-08-2024	Via Verde	-3,91
16		06-08-2024	Levantamento Carnaxide	-60,00
17		07-08-2024	Levantamento Lisboa	-60,00
18		08-08-2024	Pagamento de conta cartão	-90,00
19		09-08-2024	Credito Habitacao Geral (166/360)	-164,66	Provision
20		10-08-2024	Via Verde	-2,62
21		11-07-2024	Via Verde	-1,62	Shipping
22
23
07-08-2024

 

[Lacan]

*Just made a simple data correction previous table.

Fórmula Principal para o Ficheiro VDF.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4
5		Data Operação	Descrição	Montante( EUR )	Category		Expenses Category	01/06/2024	01/07/2024	01/08/2024
6		01-06-2024	Miami Beach Club	-100,00	Restaurant		Provision
7		06-06-2024	Credito Habitacao Geral (166/360)	-164,66	Provision		Restaurant
8		09-07-2024	Credito Habitacao Geral (166/360)	-164,66	Provision		Shipping
9		30-07-2024	Transferência de Mangopay	20,00
10		31-07-2024	Ordenado	1 500,00
11		01-08-2024	Aegon Santander Portugal Nao Vida	-17,91
12		02-08-2024	Pagamento de conta cartão	-200,00	Shipping
13		03-08-2024	Hyde Vegas	-50,00	Restaurant
14		04-08-2024	Pagamento de conta cartão	-200,00
15		05-08-2024	Via Verde	-3,91
16		06-08-2024	Levantamento Carnaxide	-60,00
17		07-08-2024	Levantamento Lisboa	-60,00
18		08-08-2024	Pagamento de conta cartão	-90,00
19		09-08-2024	Credito Habitacao Geral (166/360)	-164,66	Provision
20		10-08-2024	Via Verde	-2,62
21		11-07-2024	Via Verde	-1,62	Shipping
22
23
24
07-08-2024

 

[hagia_sofia]

Hello,

I am not sure whether you want to sum by day or month; if it is the latter, then e.g.

=SUMPRODUCT(($D$6:$D$21)*(EOMONTH(+$B$6:$B$21,-1)+1=H$5)*($E$6:$E$21=$G6))

 

[Lacan]

Hello,

I am not sure whether you want to sum by day or month; if it is the latter, then e.g.

=SUMPRODUCT(($D$6:$D$21)*(EOMONTH(+$B$6:$B$21,-1)+1=H$5)*($E$6:$E$21=$G6))

Dear @hagia_sofia hope your fine ,

Yes please, would like to sum by Month.
It brings 0 in all references, can you please check formula because it seems it is correct?
Thank you so much!!!

 

[hagia_sofia]

Hm, hard to tell; is it the same with this:

=SUM(FILTER($D$6:$D$21,($E$6:$E$21=$G6)*(MONTH($B$6:$B$21)=MONTH(H$5))*(YEAR($B$6:$B$21)=YEAR(H$5)),0))

 

[Lacan]

Hm, hard to tell; is it the same with this:

=SUM(FILTER($D$6:$D$21,($E$6:$E$21=$G6)*(MONTH($B$6:$B$21)=MONTH(H$5))*(YEAR($B$6:$B$21)=YEAR(H$5)),0))

Dear @hagia_sofia

This last one worked very well!!!
Emotionally would prefer the first one because its more simple and more short. But anyway this one do the job!
Maybe have to reach you again if I had to make some adjustments.
Thank you very much!!!
All the best!