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

L

Lacan

Board Regular
Joined
Oct 5, 2016
Messages
198
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJK
1
2
3
4
5Data OperaçãoDescriçãoMontante( EUR )CategoryExpenses Category01/06/202401/07/202401/07/20242
601-06-2024Miami Beach Club-100,00RestaurantProvision
706-06-2024Credito Habitacao Geral (166/360)-164,66ProvisionRestaurant
809-07-2024Credito Habitacao Geral (166/360)-164,66ProvisionShipping
930-07-2024Transferência de Mangopay20,00
1031-07-2024Ordenado 1 500,00
1101-08-2024Aegon Santander Portugal Nao Vida-17,91
1202-08-2024Pagamento de conta cartão-200,00Shipping
1303-08-2024Hyde Vegas-50,00Restaurant
1404-08-2024Pagamento de conta cartão-200,00
1505-08-2024Via Verde-3,91
1606-08-2024Levantamento Carnaxide-60,00
1707-08-2024Levantamento Lisboa-60,00
1808-08-2024Pagamento de conta cartão-90,00
1909-08-2024Credito Habitacao Geral (166/360)-164,66Provision
2010-08-2024Via Verde-2,62
2111-07-2024Via Verde-1,62Shipping
22
23
07-08-2024
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
*Just made a simple data correction previous table.

Fórmula Principal para o Ficheiro VDF.xlsm
ABCDEFGHIJK
1
2
3
4
5Data OperaçãoDescriçãoMontante( EUR )CategoryExpenses Category01/06/202401/07/202401/08/2024
601-06-2024Miami Beach Club-100,00RestaurantProvision
706-06-2024Credito Habitacao Geral (166/360)-164,66ProvisionRestaurant
809-07-2024Credito Habitacao Geral (166/360)-164,66ProvisionShipping
930-07-2024Transferência de Mangopay20,00
1031-07-2024Ordenado 1 500,00
1101-08-2024Aegon Santander Portugal Nao Vida-17,91
1202-08-2024Pagamento de conta cartão-200,00Shipping
1303-08-2024Hyde Vegas-50,00Restaurant
1404-08-2024Pagamento de conta cartão-200,00
1505-08-2024Via Verde-3,91
1606-08-2024Levantamento Carnaxide-60,00
1707-08-2024Levantamento Lisboa-60,00
1808-08-2024Pagamento de conta cartão-90,00
1909-08-2024Credito Habitacao Geral (166/360)-164,66Provision
2010-08-2024Via Verde-2,62
2111-07-2024Via Verde-1,62Shipping
22
23
24
07-08-2024
 
Upvote 0
Hello,

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

Excel Formula: 
=SUMPRODUCT(($D$6:$D$21)*(EOMONTH(+$B$6:$B$21,-1)+1=H$5)*($E$6:$E$21=$G6))
 
Upvote 0
hagia_sofia said:
Hello,

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

Excel Formula: 
=SUMPRODUCT(($D$6:$D$21)*(EOMONTH(+$B$6:$B$21,-1)+1=H$5)*($E$6:$E$21=$G6))
Click to expand...
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!!!👍👍
 
Upvote 0
Hm, hard to tell; is it the same with this:

Excel Formula: 
=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))
 
Upvote 0
Solution
hagia_sofia said:
Hm, hard to tell; is it the same with this:

Excel Formula: 
=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))
Click to expand...
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!
 
Upvote 1
You must log in or register to reply here.

Forum statistics

Threads
1,221,310
Messages
6,159,173
Members
451,543
Latest member
cesymcox

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top