SUMIF with MOD

[MossackFonseca]

Hello!

Perhaps you could help ...

Column A value
Column B item start date
Column C item end date

Need to sum only the values that have item duration divisible by 12.

I believe it is a combination of SUMPRODUCT, MOD, DATEDIF and perhaps "--", but not sure how to put them together. I know about the option to create a separate column, but need it in one single formula.

Any help is much appreciated! Thank you!

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book4
	A	B	C	D	E
1	Value	Start Date	End Date		Sum of 12X duration
2	1	1/1/2021	1/3/2021		11
3	2	2/1/2021	2/13/2021
4	3	3/1/2021	3/25/2021
5	4	4/1/2021	4/2/2021
6	5	4/10/2021	5/10/2021
7	6	7/1/2021	8/30/2021
Sheet11

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(A2:A7,--(MOD(C2:C7-B2:B7,12)=0))

 

[MossackFonseca]

Thank you! It works!

How can I add another criteria to this formula above: sum those values for which the end date is higher than x?

 

[Eric W]

Easy enough:

Book1
	A	B	C	D	E	F
1	Value	Start Date	End Date		Sum of 12X duration	Date
2	1	1/1/2021	1/3/2021		9	3/1/2021
3	2	2/1/2021	2/13/2021
4	3	3/1/2021	3/25/2021
5	4	4/1/2021	4/2/2021
6	5	4/10/2021	5/10/2021
7	6	7/1/2021	8/30/2021
Sheet3

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(A2:A7,--(MOD(C2:C7-B2:B7,12)=0),--(C2:C7>F2))