EXCL HELP

[rsd007]

Hello,
I dont have much experience with EXCL. I am trying to do a project where I have to automate the total for a month in E column based on the DATE column B. I dont know how to do a Formula so it can look in Date column and add cash out/in for the month.
I have downloaded XL2BB but for some problem was not allowing to convert so had to do this

 

[AhoyNC]

Maybe something like this:

Book1
	A	B	C	D	E	F
1		Date	In/Out		Month	Total
2		January 2, 2022	1		1	2
3		January 5, 2022	-2		2	9
4		January 6, 2022	3
5		February 8, 2022	4
6		February 22, 2022	5
Sheet5

Cell Formulas
Range	Formula
F2:F3	F2	=SUMPRODUCT(--(MONTH($B$2:$B$6)=E2),$C$2:$C$6)

 

[rsd007]

Maybe something like this:

Book1
	A	B	C	D	E	F
1		Date	In/Out		Month	Total
2		January 2, 2022	1		1	2
3		January 5, 2022	-2		2	9
4		January 6, 2022	3
5		February 8, 2022	4
6		February 22, 2022	5
Sheet5

Cell Formulas
Range	Formula
F2:F3	F2	=SUMPRODUCT(--(MONTH($B$2:$B$6)=E2),$C$2:$C$6)

IS it possible without entering month numbers in Column E

 

[AhoyNC]

You mean something like this?

You will need to enter a date like 1/1/22 and then custom format as mmmm to show just month.

Book1
	A	B	C	D	E	F
1		Date	In/Out		Month	Total
2		January 2, 2022	1		January	2
3		January 5, 2022	-2		February	9
4		January 6, 2022	3
5		February 8, 2022	4
6		February 22, 2022	5
Sheet5

Cell Formulas
Range	Formula
F2:F3	F2	=SUMPRODUCT(--(MONTH($B$2:$B$6)=MONTH(E2)),$C$2:$C$6)

 

[rsd007]

I dont have Month Column, whenever Month (also this sheet have info for multiple years) change in Date column Total Column shows IN/OUT for that month

 

[AhoyNC]

Try:

Book1
	A	B	C	D	E
1		Date		In/Out	Total/Month
2		January 2, 2022		1	2
3		January 5, 2022		-2
4		January 6, 2022		3
5		February 8, 2022		4	9
6		February 22, 2022		5
7		March 2, 2022		12	25
8		March 6, 2022		13
9		January 2, 2023		14	8
10		January 8, 2023		-6
11		April 5, 2023		16	16
Sheet6

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(--(YEAR($B$2:$B$11)=YEAR(B2)),--(MONTH($B$2:$B$11)=MONTH(B2)),$D$2:$D$11)
E3:E11	E3	=IF(MONTH(B3)=MONTH(B2),"",SUMPRODUCT(--(YEAR($B$2:$B$11)=YEAR(B3)),--(MONTH($B$2:$B$11)=MONTH(B3)),$D$2:$D$11))

 

[rsd007]

=IF(MONTH(B3)=MONTH(B2),"",SUMPRODUCT(--(YEAR($B$2:$B$11)=YEAR(B3)),--(MONTH($B$2:$B$11)=MONTH(B3)),$D$2:$D$11))

Thankyou for the help. I will love to understand more so I can support others. Always looking for learning opportunity

 

[AhoyNC]

You're welcome. Thanks for the feedback.