Retrieve info with different date formats

[tigerzen]

Would like to try and have the column in yellow fill with a formula/function, the monthly summed values come from the table in Columns G&H. Can I please have this in Excel 2019. The challenge is with the 2 different date formats.

Book6
	A	B	C	D	E	F	G	H
2		Date	Amount	Contributions			Date	Contributions
3		1/12/2021	$ 1,000				Feb-22	$ 100
4		1/01/2022	$ 1,500				Feb-22	$ 200
5		1/02/2022	$ 2,500	$450			Feb-22	$ 150
6		1/03/2022	$ 4,000				Apr-22	$ 300
7		1/04/2022	$ 6,000	$300			May-22	$ 200
8		1/05/2022	$ 8,000	$500			May-22	$ 300
9		1/06/2022	$ 12,000
10		1/07/2022	$ 15,000
Sheet1

 

[hagia_sofia]

Hello, if all the dates are "1st of a month" as it is above, then:

=IF(SUMIFS($H$2:$H$7,$G$2:$G$7,B2)=0,"",SUMIFS($H$2:$H$7,$G$2:$G$7,B2))

 

[tigerzen]

Thanks Hagia_sofia that works fine. If the dates in column B were not the 1st of the month eg 5/1/2022 or 19/1/2022 what would you modify in the formula to make it work?

 

[bebo021999]

Two options: Using SUMIFS or SUMPRODUCT

Book1
	A	B	C	D	E	F	G	H
1				Using SUMIFS	Using SUMPRODUCT
2		Date	Amount	Contributions			Date	Contributions
3	1	12-Jan-21	1000	0	0		22-Feb	100
4	1	1-Jan-22	1500	0	0		22-Feb	200
5	2	1-Feb-22	2500	450	450		22-Feb	150
6	3	1-Mar-22	4000	0	0		22-Apr	300
7	4	1-Apr-22	6000	300	300		22-May	200
8	5	1-May-22	8000	500	500		22-May	300
9	6	1-Jun-22	12000	0	0
10	7	1-Jul-22	15000	0	0
Sheet1

Cell Formulas
Range	Formula
D3:D10	D3	=SUMIFS($H$3:$H$8,$G$3:$G$8,">=" & B3,$G$3:$G$8,"<=" & EOMONTH(B3,0))
E3:E10	E3	=SUMPRODUCT((TEXT($G$3:$G$8,"mm/yyyy")=TEXT(B3,"mm/yyyy"))*$H$3:$H$8)
A3:A10	A3	=MONTH(B3)

 

[tigerzen]

Two options: Using SUMIFS or SUMPRODUCT

Book1
	A	B	C	D	E	F	G	H
1				Using SUMIFS	Using SUMPRODUCT
2		Date	Amount	Contributions			Date	Contributions
3	1	12-Jan-21	1000	0	0		22-Feb	100
4	1	1-Jan-22	1500	0	0		22-Feb	200
5	2	1-Feb-22	2500	450	450		22-Feb	150
6	3	1-Mar-22	4000	0	0		22-Apr	300
7	4	1-Apr-22	6000	300	300		22-May	200
8	5	1-May-22	8000	500	500		22-May	300
9	6	1-Jun-22	12000	0	0
10	7	1-Jul-22	15000	0	0
Sheet1

Cell Formulas
Range	Formula
D3:D10	D3	=SUMIFS($H$3:$H$8,$G$3:$G$8,">=" & B3,$G$3:$G$8,"<=" & EOMONTH(B3,0))
E3:E10	E3	=SUMPRODUCT((TEXT($G$3:$G$8,"mm/yyyy")=TEXT(B3,"mm/yyyy"))*$H$3:$H$8)
A3:A10	A3	=MONTH(B3)

Thanks Bebo, the formulae look good. When I copy your solution into a new file, both the SUMIFs and SUMPRODUCT formulae return 0s for all the cells. The other issue is that your MONTH formula will only work for one year ie if there is Feb 2022 and Feb 2023 then the formulae picks up for all Feb and I need it to be different for each Feb period.

 

[bebo021999]

Try to attach new mini sheet with results of 0s?

 

[tigerzen]

Try to attach new mini sheet with results of 0s?

Book3
	A	B	C	D	E	F	G	H
1				Using SUMIFS	Using SUMPRODUCT
2		Date	Amount	Contributions			Date	Contributions
3	1	12-Jan-21	1000	0	0		22-Feb	100
4	1	1-Jan-22	1500	0	0		22-Feb	200
5	2	1-Feb-22	2500	0	0		22-Feb	150
6	3	1-Mar-22	4000	0	0		22-Apr	300
7	4	1-Apr-22	6000	0	0		22-May	200
8	5	1-May-22	8000	0	0		22-May	300
9	6	1-Jun-22	12000	0	0
10	7	1-Jul-22	15000	0	0
Sheet1

Cell Formulas
Range	Formula
D3:D10	D3	=SUMIFS($H$3:$H$8,$G$3:$G$8,">=" & B3,$G$3:$G$8,"<=" & EOMONTH(B3,0))
E3:E10	E3	=SUMPRODUCT((TEXT($G$3:$G$8,"mm/yyyy")=TEXT(B3,"mm/yyyy"))*$H$3:$H$8)
A3:A10	A3	=MONTH(B3)

 

[Alex Blakenburg]

The issue is not with bebo's formula but something has gone wrong with the dates in column G.
Just copy your original dates in column G in post #1 to the Book3 data you just posted and it should work fine.

 

[kvsrinivasamurthy]

Try. In D3 copied down

=SUMPRODUCT($H$3:$H$8*(MONTH($G$3:$G$8)=MONTH($B3))*(YEAR($G$3:$G$8)=YEAR($B3)))

 

[tigerzen]

The issue is not with bebo's formula but something has gone wrong with the dates in column G.
Just copy your original dates in column G in post #1 to the Book3 data you just posted and it should work fine.

Thanks Alex, you're right, something happens to the date format with the XLBB process. Once format is corrected, formulae work well.