# COUNTIF with a variable 365 day range

#### bchez

##### Board Regular
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

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### Tetra201

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
You are welcome.

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

#### FDibbins

##### Well-known Member
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

##### Board Regular
You are welcome.

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

Thank you Tetra201 I will try this.

#### bchez

##### Board Regular
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

##### Well-known Member
let me know how you make out

#### bchez

##### Board Regular
let me know how you make out

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

#### bchez

##### Board Regular
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>

Replies
0
Views
105
Replies
12
Views
616
Replies
14
Views
630
Replies
1
Views
215
Replies
0
Views
484

1,195,625
Messages
6,010,754
Members
441,568
Latest member
abbyabby

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back