Help with formulas

[joakimh]

Dear all!
I need help with some formulas.

See attached picture below

Orange area (Rows A-D) contains an export from my bank with all payments I have done.
Row E will be categories which I will make myself.

Yellow area (row H and I) will be total amount of each category during the year..
This one works using following formula: =SUM.IF(E2:E50; H2&"*"; D2:D50)

But the last green area (Row K-V) is my problem.
I made an exemple how I would like to have it, but I need help with formulas.

Here I would to take the total montly amount of each category and month and place it in each month.
I guess I need to do some SUMIF as well, but I cant get it to work?!

Please help

 

[SanjayGMusafir]

Since you haven't specified how you have put months on top, so I'm giving you example for January only and you can improvise, But there can be a dynamic way of dong same...

=SUMIFS(D:D,E:E,$H2,A:A,">="&Date(2023,1,1),A:A,"<="&Date(2023,1,31))

 

[SanjayGMusafir]

The dynamic way is to use Formula in Cell K1

=DATE(2023,SEQUENCE(1,12,1,1),1)

Book1
	K	L	M	N	O	P	Q	R	S	T	U	V
1	January	February	March	April	May	June	July	August	September	October	November	December
Sheet1

Cell Formulas
Range	Formula
K1:V1	K1	=DATE(2023,SEQUENCE(1,12,1,1),1)
Dynamic array formulas.

Formatting cells from K1 onwards as "mmmm"

And then use -

=SUMIFS(D:D,E:E,$H2,A:A,">="&K$1,A:A,"<="&Eomonth(K$1,0))

 

[joakimh]

Dear all!
I need help with some formulas.

See attached picture below

Orange area (Rows A-D) contains an export from my bank with all payments I have done.
Row E will be categories which I will make myself.

Yellow area (row H and I) will be total amount of each category during the year..
This one works using following formula: =SUM.IF(E2:E50; H2&"*"; D2:D50)

But the last green area (Row K-V) is my problem.
I made an exemple how I would like to have it, but I need help with formulas.

Here I would to take the total montly amount of each category and month and place it in each month.
I guess I need to do some SUMIF as well, but I cant get it to work?!

View attachment 101580

Please help

Since you haven't specified how you have put months on top, so I'm giving you example for January only and you can improvise, But there can be a dynamic way of dong same...

=SUMIFS(D:D,E:E,$H2,A:A,">="&Date(2023,1,1),A:A,"<="&Date(2023,1,31))

Here is an exmple file: Excel Help Forum
Couldnt ge tthe "upload mini-sheet" to work

 

[joakimh]

The dynamic way is to use Formula in Cell K1

=DATE(2023,SEQUENCE(1,12,1,1),1)

Book1
	K	L	M	N	O	P	Q	R	S	T	U	V
1	January	February	March	April	May	June	July	August	September	October	November	December
Sheet1

Cell Formulas
Range	Formula
K1:V1	K1	=DATE(2023,SEQUENCE(1,12,1,1),1)
Dynamic array formulas.

Formatting cells from K1 onwards as "mmmm"

And then use -

=SUMIFS(D:D,E:E,$H2,A:A,">="&K$1,A:A,"<="&Eomonth(K$1,0))

My months was jused typed in manually. no formula
But now when I tried using your =DATE(2023,SEQUENCE(1,12,1,1),1) , then it says the standard error "there is a problem using this formula..."

is it becuase of a swedish Excel version?

 

[SanjayGMusafir]

My months was jused typed in manually. no formula

Try this -

Excel test.xlsx
	A	B	C	D	E	F		H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Date	Specification	Location	Ammount	Category			Category	Yearly cost		January	February	March	April	May	June	July	August	September	October	November	December
2	7/7/23	Storenamne X on bill	New york	39.00	Restaurants			Restaurants	289 kr		0.00	250.00	0.00	0.00	0.00	0.00	39.00	0.00	0.00	0.00	0.00	0.00
3	1/30/23	Storenamne Y on bill	Berlin	101.00	Clothes			Shopping online	100 kr		99.99	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
4	2/10/23	Storenamne Z on bill	London	100.00	Restaurants			Clothes	101 kr		101.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
5	8/22/23	Storenamne A on bill	Stockholm	32.00	Streaming			House Expense	495 kr		0.00	0.00	0.00	495.45	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
6	1/3/23	Storenamne B on bill	WWW.AMAZON.COM	99.99	Shopping online			Car	86 kr		85.79	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
7	4/3/23	Storenamne C on bill	Paris	495.45	House expense			Telephone extense	99 kr		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
8	2/3/23	Storenamne 1 on bill	633-2321-043443	99.00	Telephone expense			Streaming	32 kr		0.00	0.00	0.00	0.00	0.00	0.00	0.00	32.00	0.00	0.00	0.00	0.00
9	1/4/23	Storenamne 2 on bill	IRVINE	85.79	Car						0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
Blad1

Cell Formulas
Range	Formula
K1:V1	K1	=DATE(2023,SEQUENCE(1,12,1,1),1)
K2:V9	K2	=SUMIFS($D:$D,$E:$E,$H2,$A:$A,">="&K$1,$A:$A,"<="&EOMONTH(K$1,0))
I2:I3,I5:I6	I2	=SUMIF(E2:E892, H2&"*", D2:D892)
Dynamic array formulas.

 

[joakimh]

Try this -

Excel test.xlsx
	A	B	C	D	E	F		H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Date	Specification	Location	Ammount	Category			Category	Yearly cost		January	February	March	April	May	June	July	August	September	October	November	December
2	7/7/23	Storenamne X on bill	New york	39.00	Restaurants			Restaurants	289 kr		0.00	250.00	0.00	0.00	0.00	0.00	39.00	0.00	0.00	0.00	0.00	0.00
3	1/30/23	Storenamne Y on bill	Berlin	101.00	Clothes			Shopping online	100 kr		99.99	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
4	2/10/23	Storenamne Z on bill	London	100.00	Restaurants			Clothes	101 kr		101.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
5	8/22/23	Storenamne A on bill	Stockholm	32.00	Streaming			House Expense	495 kr		0.00	0.00	0.00	495.45	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
6	1/3/23	Storenamne B on bill	WWW.AMAZON.COM	99.99	Shopping online			Car	86 kr		85.79	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
7	4/3/23	Storenamne C on bill	Paris	495.45	House expense			Telephone extense	99 kr		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
8	2/3/23	Storenamne 1 on bill	633-2321-043443	99.00	Telephone expense			Streaming	32 kr		0.00	0.00	0.00	0.00	0.00	0.00	0.00	32.00	0.00	0.00	0.00	0.00
9	1/4/23	Storenamne 2 on bill	IRVINE	85.79	Car						0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
Blad1

Cell Formulas
Range	Formula
K1:V1	K1	=DATE(2023,SEQUENCE(1,12,1,1),1)
K2:V9	K2	=SUMIFS($D:$D,$E:$E,$H2,$A:$A,">="&K$1,$A:$A,"<="&EOMONTH(K$1,0))
I2:I3,I5:I6	I2	=SUMIF(E2:E892, H2&"*", D2:D892)
Dynamic array formulas.

thanks for quick replies, really appricated!

still problem, is below

 

[SanjayGMusafir]

Unable to understand the problem from the screenshot

 

[joakimh]

Unable to understand the problem from the screenshot

Would you be able and download my excel file ? Excel Help Forum
And test it?

Could it be something with comma and semicolon due to swedish? I know we sometimes use semicolon..
But show should the formula be then? :s

 

[SanjayGMusafir]

Would you be able and download my excel file ? Excel Help Forum
And test it?

Check post #6 I already did that...

Check that and revert...