EXCEL - Summing IF certain criteria occur 2

[Llwynog]

Sorry - but the question's got to be asked !!

We've got a spreadsheet that we enter simple daily data in e.g. if someone is off sick for 1 day the entry is 1, if their off for 2 days, the entry is per day i.e. 1, 1, and then totalled in the end column.

What we want to be able do is to have a total showing when there has been a period of >= 28 consecutive day.
The S/S is set out like this

Name 1/1/07 2/1/07 3/1/07 ... 31/12/07 TOTAL
A.N.Other 1 1 2

Any help would be greatly appreciated
Thanks, and regards

 

[mintjulep]

Perhaps the SUMIF() function?

 

[yogia]

Hi Llwynog:

As mintjulep has stated chaces are you need the SUMIF function, or may be even the COUNT function ... however, it is not clear where do you want to have the 'total showing when there has been a period of >= 28 consecutive day ... how about posting some sample data for this scrnario.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[xlhelp]

Yes, questions definitely gotta be asked.....

Mine is how do you manage to fit the whole year in 256 columns? Have you skipped Saturdays and Sundays? Even then you require minimum of 264 columns if you don't count holidays.

Is this really the best way to track absenteeism?

Have you thought about what stats other than your 28 day period would you want in the future?

I only ask because I had a company keep track of their employees this way and ended up paying the company where I worked a couple of thousand to sort the data at the end of the year.

Member- AAAA Association Against Acronym Abusers

 

[Llwynog]

Thanks for your replies.

Yogia - sample data - where would you like it sent please.

Xhelp - you are quite right - this is not the best way of dealing/recording absenteeism. however it's only a stop gap measure before we go onto an Electronic Staff Record system.
I use two worksheets to hold the data (using the usual referencing) to overcome the 256 column problem.

 

[xlhelp]

If you go to Yogi's website his e-mail address is right there.... at the bottom

Member- AAAA Association Against Acronym Abusers

 

[AnotherHiggins]

Howdy.

Emailing example files directly to other members is seriously frowned upon here at Tek-Tips. The reason is that other members search the forums for answers to their problems and should be able to follow the logic of how a problem is solved from start to finish just by reading a thread.

You can post a very short example of your data right here on the site - just type it in. It might help to use the [ignore][tt][/tt][/ignore] TGML tags to make your data more legible. (Click the "Process TGML" link under the posting window for more info).

But, honestly, I don't see any reason for you to post example data. I have a very clear idea of how your data is organized.

That brings me to my next point, Llwynog. This is a terrible spreadsheet design - even for a stopgap.

How many employees are you tracking? Less than 255? If so, then simply transposing the sheet to have names across the top and dates going down would be an improvement. Still not elegant by any stretch, but definitely better than having two different sheets to store the data.

Llwynog: Are you willing to consider other layouts that might make it easier to accomplish your goals?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[yogia]

Hi John:

Thanks for clarifying to Llwynog ... any sample data that needs to be posted should be posted right on tek-tips site ... so that we can all benefit from the discussions and the posted solutions.



Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[xlhelp]

I would suggest that if you are only tracking absenteeism, then the following model would be simple and painless.

[tt]xlhelp 3 2007/04/25 2007/04/30 2007/05/03
anotherhiggins 2 2007/03/01 2007/04/30 2007/05/02
[/tt]

where names are in Column A, days abesent in column B and the actual dates a person is absent is entered in the columns. I have used a reference of C:Z, but any reference can be used.

The formula in column B would then be
=COUNTIF(C2:Z2,">"&(NOW()-28))

So the results above show that xlhelp was absent three time in the last 28 days and our friend John was absent twice.

Member- AAAA Association Against Acronym Abusers

 

[AnotherHiggins]

Sorry if I came across as a jerk before, xlhelp and yogia. I re-read my previous post and it reads more harshly than I meant for it to.

Anyway - I'm convinced that there is a clever way to use an Array Formula to answer the question, but it eludes me.

In the mean time, Llwynog, here is a UDF (user defined function) that will return the max count of contiguous non-null cells. I again urge you to restructure your data. Consider this scenario: there are 30 days straight, but they span the two different sheets. What a pain that will be to calculate!

Function MaxDays(rng As Range) As Long

For Each cll In rng
    If cll.Value = 1 Then
        CrntCount = CrntCount + 1
    Else
        MaxDays = Application.WorksheetFunction.Max(MaxDays, CrntCount)
        CrntCount = 0
    End If
Next cll

End Function

Or to generalize it so that you can use any character in a cell (not just "1"), change to the following (change marked in blue):

Function MaxDays(rng As Range) As Long

For Each cll In rng
    If cll.Value [blue]<> ""[/blue] Then
        CrntCount = CrntCount + 1
    Else
        MaxDays = Application.WorksheetFunction.Max(MaxDays, CrntCount)
        CrntCount = 0
    End If
Next cll

End Function

If you have any questions about where to put the code, see:
faq707-5758.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.