To count values in a changeble range

[souza.eq]

I need some help with a code to the workbook uploaded. I'm having a hard time in finding a way to start!
The workbook is to control day works of employes where they cannot work more than 183 days in the last 365 continuous days.
I need a code where based on the selected cell it shows me anywhere in the sheet how many days the employe worked in the last 365 days and how many days off he had, this way it will be possible to plan how many days I can use someones work without extrapolate the limit easyer then counting manually every time I need to do that.
E= work
F= day off
V= trip (same as work)

If some arrangement need to be done to make the code easier its ok.

I started thinking in using a countif, but i didint know how to make count works backwards 365 days based on the selected cell.

Then now i'm trying to use a matrix and array to store the values then based on the cell, but i'm also having trouble in make it work on the 3 years range as shown on the file uploaded.

Thank You all,
Sorry for my bad english, I'm trying to improve!

[SamT]

For 2012. Change the Range for other years
=COUNTIF(B3:AF14,"E")+COUNTIF(B3:AF14,"V")

[p45cal]

An unsophisticated solution in the attached file; double-click a cell and a message box should pop-up. Makes lots of assumptions. Comments in the code too.
Code in the sheet concerned's code-module:Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim partRowEnd As Range, partRowStart As Range
Cancel = True
Set mainblock = Range(Cells(Application.Max(3, Target.Row - 16), 2), Cells(Application.Max(Target.Row - 1, 3), 32))
On Error Resume Next
Set partRowStart = Target.Offset(-17).Resize(, 33 - Target.Column)
Set partRowEnd = Cells(Target.Row, 2).Resize(, Target.Column - 2)
On Error GoTo 0
If Not partRowStart Is Nothing Then
x = x + Application.CountIf(partRowStart, "E")
x = x + Application.CountIf(partRowStart, "V")
y = y + Application.CountIf(partRowStart, "F")
End If
x = x + Application.CountIf(mainblock, "E")
x = x + Application.CountIf(mainblock, "V")
y = y + Application.CountIf(mainblock, "F")
If Not partRowEnd Is Nothing Then
x = x + Application.CountIf(partRowEnd, "V")
x = x + Application.CountIf(partRowEnd, "E")
y = y + Application.CountIf(partRowEnd, "F")
End If
Set AllDates = mainblock
If Not partRowStart Is Nothing Then Set AllDates = Union(partRowStart, AllDates)
If Not partRowEnd Is Nothing Then Set AllDates = Union(partRowEnd, AllDates)
AllDates.Select 'debug line can be deleted
MsgBox x & " days worked" & vbLf & y & " days off"
Target.Select 'debug line can be deleted
End Sub

ps. Totals don't add to 365/366 because you have some months with 31 days in that don't have 31 days.

[souza.eq]

Very nice, Thank You. I would never get there. I only know the basic programming language for fortran from eng. School. How do I learn VBA like that? Any book you recommend?

Backing to work!
There is a way to count the blank cells (not valid days for the year) and then make the selection go this days further. Like there was 5 blank cells the selection will run back 5 more cells?

[p45cal]

Any book you recommend?
John Walkenbach's Excel 20nn Power Programming: http://www.amazon.co.uk/Excel-Power-Programming-Spreadsheets-Bookshelf/dp/0470475358/ref=sr_1_1?ie=UTF8&qid=1372436948&sr=8-1&keywords=walkenbach+power+programming


There is a way to count the blank cells (not valid days for the year) and then make the selection go this days further. Like there was 5 blank cells the selection will run back 5 more cells?Is this to compensate for something? I like to know why since there may be an easier way..

[souza.eq]

Is this to compensate for something? I like to know why since there may be an easier way..

Its becouse this international workers cant stay in my country more then 183 days in the last 365 running days (consecutive), so it has to count exactly 365 days.

[p45cal]

Then all you have to do is make sure that the cells for invalid dates do not have anything in them (31 Sept and 31 Oct 2013 should be blank on your spreadsheet) then things do add upto 365.

You've got me worried with the consecutive bit though!

[souza.eq]

Then all you have to do is make sure that the cells for invalid dates do not have anything in them (31 Sept and 31 Oct 2013 should be blank on your spreadsheet) then things do add upto 365.

You've got me worried with the consecutive bit though!

First thank you so much for the help!

Trying to explain my self better, I'm updating another sheet showing exactly my need. The last code was great, but I need a sheet that count the last 365 valid days (just don’t count the empty cells) at each day (as the code you provided) and mark (in red or other way) automatically the cell if the counting of "E" is greater than 183.

This should run automatically at any change of values/inputs in the sheet (without being necessary to run the macro with a button or a click, I think it can be done to make the code run when there is a change on the interval), always counting the last 365 days, with the condition of showing the user when the number of "E"s are greater than 183.

I'll tell what I have to do to try making it clearer. I have to plan the work days of more than 30 employees that can't stay more than 183 days in my country (Brazil) in the last 365 running days. Their shift is 28(working = E) x 28 (resting = F). The days that they are in the country not working also counts for the matter, I call these days of tripping (V). So every month I have to count the last 365 days of each one and check if they have or are near to pass more than 183 days in the country. It’s quite a job to do this for every one and a need a way to make it easier to save time for other activities. So the idea is filling every day the sheet and when the counting of "E" reaches more than 183 days the sheet will alert me about the limit.
I really hope you all can help me, I'm not just asking for codes, I'm learning and trying to right the code too, but I don't have this level of expertise yet. Thank you for any help! Really appreciate.

Have a good weak.

[p45cal]

In the attached there is no code, only conditional formatting counting the total of the previous 365 days' count of cells containing E or V, then highlighting the cell if the total comes to more than 183.
There are 2 different conditional formatting formulae:
1. Applied to C13:AF37
2. Applied to AG13:AG37

[souza.eq]

In the attached there is no code, only conditional formatting counting the total of the previous 365 days' count of cells containing E or V, then highlighting the cell if the total comes to more than 183.
There are 2 different conditional formatting formulae:
1. Applied to C13:AF37
2. Applied to AG13:AG37


Great man exactly that what I needed. Perfect! I have to study more how to work with range and offset. Now I'll try to put the conditions on my original sheet! Thanks P45CAL! :thumb

Can you explain to me the code =COUNTIF(OFFSET($C13,0,0,1,31),"E")+COUNTIF(OFFSET($C13,0,0,1,31),"V")+COUNTIF(OFFSET($C13,-11,0,11,31),"E")+COUNTIF(OFFSET($C13,-11,0,11,31),"V")>183

what OFFSET($C13,0,0,1,31),"E" does? How it works?

And why 2 ranges? they have to be like that to work?

[p45cal]

Formula in AR13 is:
=COUNTIF(OFFSET(C13,-12,1,1,33-COLUMN(C13)),"E")+COUNTIF(OFFSET(C13,-12,1,1,33-COLUMN(C13)),"V")+COUNTIF(OFFSET($C13,0,0,1,COLUMN(C13)-2),"E")+COUNTIF(OFFSET($C13,0,0,1,COLUMN(C13)-2),"V")+COUNTIF(OFFSET($C13,-11,0,11,31),"E")+COUNTIF(OFFSET($C13,-11,0,11,31),"V")
which when split up looks like:
=COUNTIF(OFFSET(C13,-12,1,1,33-COLUMN(C13)),"E")
+COUNTIF(OFFSET(C13,-12,1,1,33-COLUMN(C13)),"V")
+COUNTIF(OFFSET($C13,0,0,1,COLUMN(C13)-2),"E")
+COUNTIF(OFFSET($C13,0,0,1,COLUMN(C13)-2),"V")
+COUNTIF(OFFSET($C13,-11,0,11,31),"E")
+COUNTIF(OFFSET($C13,-11,0,11,31),"V")

pairing them off:
=COUNTIF(OFFSET(C13,-12,1,1,33-COLUMN(C13)),"E")
+COUNTIF(OFFSET(C13,-12,1,1,33-COLUMN(C13)),"V")

+COUNTIF(OFFSET($C13,0,0,1,COLUMN(C13)-2),"E")
+COUNTIF(OFFSET($C13,0,0,1,COLUMN(C13)-2),"V")

+COUNTIF(OFFSET($C13,-11,0,11,31),"E")
+COUNTIF(OFFSET($C13,-11,0,11,31),"V")

Of each pair, one counts the Es, the other the Vs.

Taking one of each pair away leaves:
=COUNTIF(OFFSET(C13,-12,1,1,33-COLUMN(C13)),"E")

+COUNTIF(OFFSET($C13,0,0,1,COLUMN(C13)-2),"E")

+COUNTIF(OFFSET($C13,-11,0,11,31),"E")

which would just count the Es.

Each of the 3 parts uses:
COUNTIF(range,"E")

The three ranges are:
The partial month at the top:
OFFSET(C13,-12,1,1,33-COLUMN(C13))

The partial month at the bottom:
OFFSET($C13,0,0,1,COLUMN(C13)-2)

The whole 11 months inbetween:
OFFSET($C13,-11,0,11,31)

I'll deal with only the first one (OFFSET(C13,-12,1,1,33-COLUMN(C13)))and you can work out the rest yourself:
Offset takes the form:
OFFSET(reference, number of rows offset, number of columns offset, height, width)
so:
reference = C13
number of rows offset = -12 (that's 12 cells up from C13)
number of columns offset = 1 (that's 1 column across from C13)
height = 1 (that's 1 cell high)
width = 33-COLUMN(C13) (that's 33-3 = 30 cells wide) COLUMN(C13) is the column number of column C, that is, 3.

All that comes to:
COUNTIF(D1:AG1,"E")

You can do the rest.

The conditional formatting takes it one step further asking if that total is greater than 183.