COUNTIF with a variable 365 day range

[bchez]

I need to track how many times employees are absent. They are allowed 5 unexcused absences in any 365 day period. My spreadsheet looks like this -
where the dates are in Col Aand the number of absences are in Col B

Date	Joe absent
1/1/2017
1/2/2017	1
1/3/2017
1/4/2017	1
1/5/2017

<tbody>
</tbody>

So on Jan 1, 2018 my count would be 2. But on Jan 3, 2018 my count would only be 1, as the absence for 1/2/2017 would have dropped off because we are using a rolling 365 day period. The previous spreadsheet owner used OFFSET, MATCH, and named ranges. I hear INDEX could work. I don't do VBA.... is there an easy way to do a COUNTIF with a 365 day rolling range? Thank you

 

[Tetra201]

Assuming your Jan 3, 2018 is entered in cell C2, try the following formula:

=SUMPRODUCT(--($A$2:$A$1000>=EDATE($C$2,-12)),--($A$2:$A$1000<$C$2),$B$2:$B$1000)

 

[bchez]

Assuming your Jan 3, 2018 is entered in cell C2, try the following formula:

=SUMPRODUCT(--($A$2:$A$1000>=EDATE($C$2,-12)),--($A$2:$A$1000<$C$2),$B$2:$B$1000)

Thank you Tetra201. That puts a 1 in there very nicely. I will try this and also try to use the TODAY function as my end date for the 365 days.

 

[Tetra201]

You are welcome.

If you need to, just replace both occurrences of $C$2 with TODAY().

 

[FDibbins]

Another option...
=COUNTIFS(A:A,"<="&C2,A:A,">="&EDATE(C2,-12),B:B,1)
countifs is more efficient then SP over large amounts of data

 

[bchez]

You are welcome.

If you need to, just replace both occurrences of $C$2 with TODAY().

Thank you Tetra201 I will try this.

 

[bchez]

Another option...
=COUNTIFS(A:A,"<="&C2,A:A,">="&EDATE(C2,-12),B:B,1)
countifs is more efficient then SP over large amounts of data

Thank you Ford - I will also try this one. My first attempt at counting things that reset every 365 days. Appreciate your help!

 

[FDibbins]

let me know how you make out

 

[bchez]

let me know how you make out

Will do. Excel is a fun program to use. Thank you again

 

[bchez]

So here is my snag with this: I have to keep track of absences using text like Child illness or Personal Illness on one tab. Then I have to count those instances on a summary tab, but I can only count the instances that happened within the past 365 days. So for example in the first tab Alvarez was out due to Personal Illness on 1/3/2017.

A	B	C
Date	Day	Alvarez, D
1/1/2017	Sun
1/2/2017	Mon
1/3/2017	Tue	Personal Illness

<tbody>
</tbody>

Below is the summary tab. So in cell B3 I need a formula for how many times Alvarez was out due to Personal Illness, but the formula needs to reset every 365 days. I liked the SUMPRODUCT, and of course I love COUNTIFS, but can't figure out how to marry them. What am I missing? I don't want to use OFFSET if possible or write code. Thanks in advance.

A	B	C
Todays Date	1/5/2017
Name	Personal Illness	Child Illness
Alvarez, D	Formula needed here

<tbody>
</tbody>