SUMIFS using date ranges and other criteria

[ivorykeys87]

Gonna try and keep this real simple to understand. I'm trying to calculate residual income from insurance policies sold a year ago. The data is as follows:

A:A = Date policies went effective
B:B= Status of policy (it must be active to pay a residual)
C:C= Residual amount to be paid.

The residual only pays once the policy has been effective for one year, and stops paying once it has been effective for 6 years. I have what I thought to be the correct formula in I6. It reads as follows

=SUMIFS(C2:C51,A2:A51,TODAY()>=DATE(YEAR(A2:A51)+1,MONTH(A2:A51),DAY(A2:A51)),A2:A51,TODAY()<=DATE(YEAR(A2:A51)+6,MONTH(A2:A51),DAY(A2:A51)),B2:B51,"Active")

This returns a "0". Any help would be greatly appreciated.

 

[Aladin Akyurek]

Is this what you mean?

=SUMIFS(C2:C51,A2:A51,">="&EDATE(TODAY(),-6*12),A2:A51,"<="&TODAY(),B2:B51,"Active")

 

[ivorykeys87]

Is this what you mean?

=SUMIFS(C2:C51,A2:A51,">="&EDATE(TODAY(),-6*12),A2:A51,"<="&TODAY(),B2:B51,"Active")

Close. This returned the value of all active policies. Seeing as how today is 9/2/2017, I need it to only return the value of policies that have been active for 1 full year or longer up to today, but not active more than 6 years. So I shouldn't have Oct, Nov and Dec active policies in the SUM since they haven't been active for a year, yet.

 

[ivorykeys87]

Here it is!

=SUMIFS(C2:C51,A2:A51,"<="&EDATE(TODAY(),-12),A2:A51,">="&EDATE(TODAY(),-12*6),B2:B51,"Active")


Wouldn't have figured it out with that initial response. Thank you very much!

 

[Aladin Akyurek]

[...]
Wouldn't have figured it out with that initial response. Thank you very much!

You are welcome.