Sumproduct/Mod/Datedif

[Tequilashot]

Hello, need some help consolidating a formula to allow me to have reaccuring quarterly costs based off multiple completion dates in a single row.

Row 18 is my desired result but without the need for rows 13-17.

I've attempted a SUMPRODUCT/MOD/DATEDIF in row 20 to try and arrive at the answer but no luck...anyone got any ideas?

Mr Excel Question 2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2		Cost per unit per quarter		500.00
3
4		Phase	Completion Date	Units
5		1	Jan-21	25
6		2	Apr-21	30
7		3	Jul-21	20
8		4	Oct-21	43
9		5	Jan-22	27
10
11		Cashflow
12		Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22
13	1	0	0	0	0	-12,500	0	0	0	-12,500	0	0	0	-12,500	0	0	0	-12,500	0	0	0	-12,500
14	2	0	0	0	0	0	0	0	-15,000	0	0	0	-15,000	0	0	0	-15,000	0	0	0	-15,000	0
15	3	0	0	0	0	0	0	0	0	0	0	-10,000	0	0	0	-10,000	0	0	0	-10,000	0	0
16	4	0	0	0	0	0	0	0	0	0	0	0	0	0	-21,500	0	0	0	-21,500	0	0	0
17	5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	-13,500	0	0	0	-13,500
18	Total	0	0	0	0	-12,500	0	0	-15,000	-12,500	0	-10,000	-15,000	-12,500	-21,500	-10,000	-15,000	-26,000	-21,500	-10,000	-15,000	-26,000
19
20	Total	#NUM!	#NUM!	#NUM!	#NUM!	#NUM!	#NUM!	#NUM!	#NUM!	#NUM!	#NUM!	#NUM!	#NUM!	#NUM!	#NUM!	#NUM!	#NUM!	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
C6:C9	C6	=EDATE(C5,3)
C12:V12	C12	=EDATE(B12,1)
B13:V17	B13	=IFERROR((MOD(DATEDIF($C5,B$12,"m"),4)=0)*-$D5*$D$2,0)
B18:V18	B18	=SUM(B13:B17)
B20:V20	B20	=SUMPRODUCT(MOD(DATEDIF($C$5:$C$9,B$12,"m"),4)=0,$D$2*$D$5:$D$9)

 

[Eric W]

Try in B20:

=SUMPRODUCT(--(B12>=$C$5:$C$9),--(MOD(MONTH(B12),4)=MOD(MONTH($C$5:$C$9),4)),$D$5:$D$9)*-$D$2

 

[Tequilashot]

Awesome thanks, worked for me!

I see the double minus sign quite often when googling around but I don't really understand them, will have to check it out further.

 

[Eric W]

Glad it works for you!

The double minus sign (or double unary) is just a way to convert a TRUE/FALSE response from a conditional to a 1/0 value so that SUMPRODUCT can multiply with it. You can find a lot about it online.