IF AND help please

[BrutalLogiC]

Hi I'm trying to write a formula in the grey cells which looks at the contract start date in column A and the current month e.g. DA30 then takes the data from the range in yellow and puts it into the corresponding month..... the start dates will be variable so I want the data in the grey cells to move when the start dates are changed

AlanY wrote a great similar formula for me previously but I can't replicate it for this problem, please help

Excel 2013/2016
	A	AR	AS	AT	AU	AV	CU	CV	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK
29			Month 0 is month before start month	Month 1 is contract start date
30	Contract Start Date	MONTH:	0	1	2	3	54	55		Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20
31	01-07-19		-	255,200	-	-	-	-						255,200
32	01-07-19		-	-	-	-	-	-
33	01-07-19		-	3,300	3,300	3,300	3,300	3,300						3,300	3,300	3,300	3,300	3,300	3,300	3,300
34	01-07-19		3,300	-	-	-	-	-					3,300
35	01-07-19		1,100	-	-	-	-	-					1,100
36	01-09-19		-	61,600	-	-	-	-								61,600
37	01-09-19		-	-	-	-	-	-
38	01-01-20		-	123,200	-	-	-	-												123,200
39	01-01-20		-	-	-	-	-	-
Summary

 

[jmancha]

Not 100% what you want to total in the yellow boxes but assuming you want to total AS31:CV31. . .

You can enter this formula in cell DA31: IF(MONTH($DA$30)=MONTH(A31),SUM(AS31:CV31),"")

This will extract the month from both fields and look for a match.

If you need to match the month AND the year you can add: IF(AND(MONTH($DA$30)=MONTH(A31),YEAR($DA$30)=YEAR(A31)),SUM(AS31:CV31),"")

 

[BrutalLogiC]

hi jmancha thanks for reply but those formulas won't work for me, I'm trying to replicate the currently hard coded values in the grey cells.

The values in yellow should be the same as the ones on the grey but will move dependent on the start date.

If the start date in column A goes from July to August then all the values in grey will move over one column to the right, the values in yellow will be static regardless of the start date.

DE31 has got 255,200 in it because it is under July-19 and A31 says the contract starts in July and AT31 says the month 1 value should be 255,200. If the start date in column A was Aug-19 then the 255,200 would come under August in DF31.

 

[BrutalLogiC]

Any suggestions please!

 

[DanteAmor]

Use the following formula:

Excel Workbook
	A	AR	AS	AT	AU	AV	AW	CT	CU	CV	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK
1
30	Contract Start Date	MONTH:	0	1	2	3			54	55		mar-19	abr-19	may-19	jun-19	jul-19	ago-19	sep-19	oct-19	nov-19	dic-19	ene-20
31	01/07/2019			255,200												255,200
32	01/07/2019				250												250
33	01/07/2019			3,300	3,300	3,300			3,300	3,300						3,300	3,300	3,300	3,300	3,300	3,300	3,300
34	01/07/2019		3,300
35	01/07/2019		1,100
36	01/09/2019			61,600														61,600
37	01/09/2019
38	01/01/2020			123,200																		123,200
39	01/01/2020
Hoja5

 

[DanteAmor]

The values ??of June 3,300 and 1,000 did not appear in the image, but the formula also finds them, as you can see in the following image:

Excel Workbook
	A	AR	AS	AT	AU	AV	AW	CT	CU	CV	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK
1
30	Contract Start Date	MONTH:		1	2	3			54	55		mar-19	abr-19	may-19	jun-19	jul-19	ago-19	sep-19	oct-19	nov-19	dic-19	ene-20
31	01/07/2019			255,200												255,200
32	01/07/2019				250												250
33	01/07/2019			3,300	3,300	3,300			3,300	3,300						3,300	3,300	3,300	3,300	3,300	3,300	3,300
34	01/07/2019		3,300												3,300
35	01/07/2019		1,100												1,100
36	01/09/2019			61,600														61,600
37	01/09/2019
38	01/01/2020			123,200																		123,200
39	01/01/2020
Hoja5

 

[BrutalLogiC]

Excellent thanks!

 

[DanteAmor]

You're welcome!