Count Specific Queries

[ladbroke]

I am trying to create a spreadsheet that allows me to track my shifts and my patterns and wish it to count how many days I've had earlies, lates and even days off based on entry.
I have taken a screenshot of Septembers shifts to show what I am trying to do but would love to be able to make the days changeable and it count for me without manually having to do it.

As you can see in the screenshot, I have dates from 1st September until 18th. I have put 14 in AD7 to show I want to count back 14 days from the last entry in cell AA.
I know I could easily make a countif (I think) that counts from a specific cell range but this cell range is fluid as more dates get added. Right now it would be (for 14 days) cell range AA7-AA20 but when I add this weeks current rota, the cells would change to AA27. But on top of that, I may want to make the days searched 21, or reduce it to 7.
Basically, I would like to see AD8 tell me how many E's are in a cell range from bottom upwards determined by how many numbers are written in AD7.
Is that possible?

 

[JEC]

Try this

=SUM(N(OFFSET(AA2,COUNTA(AA3:AA300),,-AD7)="E"))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ladbroke]

Try this

=SUM(N(OFFSET(AA2,COUNTA(AA3:AA300),,-AD7)="E"))

Thank you so much. Worked how I wanted.

 

[JEC]

Nice! You're welcome