Free Microsoft Excel 2013 Quick Reference

Attendance formula Results

I am brand new to the forum, although I do not consider myself a "newbie" to Excel.

I am having a very hard time with the following:
This is a sheet to track attendance
Column A = codes
Column B = names
Column C-BN = Dates of the year

I need to find out how many people attended that have a specific code listed in column A

So, if John Doe has a specific code that I am looking for and he is markes as Yes in column H for attending, as well as Jane Doe and so one, I need to find the running total.

Hi in the attached file i have prepared the format of employee attendace tracker. In the first sheet(leave details) i wanted to know how many leave for each employee is pending for the whole year, i have put the COUNTIF formula in cell E4 but it gives me error message "circula cell referen

=COUNTIF(January!E4:AI4,"ML").=COUNTIF(January!E4:AI4,"ML")but this formula is only caluculating only for january and i wanted to calculate for whole year.Leave details are on first sheet Shows number of maximum number of days an employee can take what leave. I am attached the file i prepared please let me know where i am making mistake. Thanks in advance.

I'm facing problems doing up the formulas for the PreSearch tab, upper and lower range month columns (columns I &J). This current database is built to record training workshops that the employees have attended. The formulas should bring about the same results as column L & M (upper and lower year columns). I.e. to ask SearchEngine macro to return the data in the required range.
For example, I want to know the employees' data for month range of Jan to Apr, it should give me data in the SearchResults for Jan to Apr only and not the rest of the months. So far I've only figured out that the formulas for "Year" range works because it is a self keyed in range while the month range is based on a drop down box linked to Miscellaneous tab. However, I do not know how may I edit the formuals so that it functions as intended. Please advise. Thanks.

I've cross-post to as I could not upload the file here. In the meanwhile, I'll try my best to upload a sample file again.

Kay Yoke

If you take a look at the spreadsheet you'll see that there are 11 sessions. I'd like to figure out a way by looking at the attendance log to get the percentage/number of people from a certain unit that attended each session. The Y's means that they were present and the N's means that they weren't.

I tried =countif(range,"12WT") which I guess is wrong because it only counts the number of times the word 12WT appears. It doesn't count the number of time a person from 12WT attended the session which is what I'm looking for. How do I go about getting the percentage of people that attended from each unit?

For all the units that didn't attend, is there a formula that would list the units that didn't attend according to the session number? For instance for session one, someone for EC, 9WT, didn't attend. Is there a way that the end result for the formula could be: EC,9WT. Or, does the result for every formula always have to be numeric?

Could some please help me? I have a spreadsheet for attendance that creates individual spreadsheets through a macro. I am trying to add a 6-week average column for each individual worksheet. I have 70 some employees so I didn't attach the whole workbook. But here is what the macro looks like:

Sub UpdateSheets()
Dim myrng As Range, ThisWeek As Date, EmpName As String, c As Range

Dim reg, hol, vac, sic

Sheets("Week End").Select
ThisWeek = Range("B2").Value
Set myrng = Worksheets("Week End").Range("a7", Range("A65536").End(xlUp))

For Each cell In myrng
EmpName = cell.Value
reg = cell.Offset(0, 1).Value
vac = cell.Offset(0, 2).Value
sic = cell.Offset(0, 3).Value
ove = cell.Offset(0, 4).Value
oth = cell.Offset(0, 5).Value
hol = cell.Offset(0, 6).Value

On Error GoTo NewSheet

With Range("A:A")
Set c = .Find(ThisWeek, LookIn:=xlFormulas)
If Not c Is Nothing Then
MsgBox ("Weekly Data for " & ThisWeek & " has already been updated.")
GoTo Finish
End If
End With


ActiveCell.Offset(1, 0).Value = ThisWeek
ActiveCell.Offset(1, 0).NumberFormat = "mm/dd/yy"
ActiveCell.Offset(1, 1).Value = reg
ActiveCell.Offset(1, 2).Value = vac
ActiveCell.Offset(1, 3).Value = sic
ActiveCell.Offset(1, 4).Value = ove
ActiveCell.Offset(1, 5).Value = oth
ActiveCell.Offset(1, 6).Value = hol
ActiveCell.Offset(1, 7).FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
Sheets("Week End").Select
Next cell
GoTo Finish

Set NewSheet = Worksheets.Add
NewSheet.Name = EmpName
Range("A1").Value = EmpName
Range("A1").Font.Size = 18
Range("A3").Value = "Week Ending"
Columns("A:A").ColumnWidth = 14
Range("b3").Value = "Regular"
Range("g3").Value = "Holiday"
Range("c3").Value = "Vacation"
Range("d3").Value = "Sick"
Range("e3").Value = "Overtime"
Range("f3").Value = "Other"
Range("h3").Value = "Total"
Columns("B:H").HorizontalAlignment = xlCenter
Range("A3:H3").Font.Bold = True
Resume FillInData

Sheets("Week End").Select

End Sub

I am not sure if I can add this function into this macro or create 70+ macros (1 for each employee) on the individual worksheets?

Any ideas or suggestions or help would be greatly appreciated.


Hey guys, its been a while since I've been here but this time its my wife that needs the help. She needs to create an attendance sheet that does a few things:

Drops points accumulated more than 365 days agoDrops 2 points if there has been no infractions in the past 30 days.
I've got some of it figured out. Here is what you will find in the demo data posted:

Dates in Column 7 are just dummy dates, used to evaluate if the formula is working.

Accumulated Points (Column H) is the total number of points that person has accumulated since counting began.

Expired Points (Column J) are points that were accumulated more than 365 days ago.You may notice that rows 3 and 4 are missing. These rows contain a "Begin Date" and "End Date" for a formula that only considers points accumulated more than 365 days ago.

Active Points is the number of points that employment decisions will be based on, the current, active count

So what is the problem? Right now, "Active Points" is off. It should be showing 3.5 points, instead of the current total of 1.5 points. If you check my formula which is:


to make it more consumable:

=If('Last Infraction' was 30 or more days ago, subtract 2 from 'Accumulated Points', if not show 'Accumulated Points')-'Expired Points'

It should be showing 3.5 because the last infraction was LESS than 30 days ago, so they don't get to drop 2 points.

I think Excel is evaluating 'Last Infraction' as a number (instead of a date), which is greater than 30, and automatically subtracting 2, regardless of the date so I need help in that regard.

Also, this table will grow with each day. We need the formula to automatically adjust itself to consider all dates up to today/current date. One last request if I may: Is there a way to have two freeze panes? She needs the names of the supervisor and agent to always be visible AND she needs columns I-O always visible. In the middle will be the range of dates from n - Today, which will be growing, which means I-O will always be getting pushed further out. And if its possible to have 'Active Points' show with a background color of Yellow when the amount is >= 8, and Red when the amount is >= 12, that would just be the icing on the cake!

Any help you can provide in this project will be MUCH appreciated, thank you!

***The file was being edited as this post was being created so some of the field/ranges may not exactly match up, please accept my apologies in this regard***

I have Windows 2000 excel 2002. The problem is as follows:
I have a template for grading courses, in the attendance sheet attendance is marked as follows: X = abscence, T = late, H = holiday and N/A is for days that the class doesn't meet. Each student is a row. I need to get the last day that that student attended with this conditions:

Dates range = e1:bi5
Students attendance range is e9:bi9.
The date of the last attendance should appear in bk9. Couldn't add the whole sheet becaus of size but there should be 54 dates, 5 months aproximadely. Thanx just for reading and I hope you can help this is the only detail left on my template.

Here's the sheet:

Formula which can calculate how many days in a month and create a column for each day. Columns for day 28, 29, 30 or 31 also need the formulas in columns 1 to 27 copied over automatically. [sheet name ďLARSheet1 to 12Ē ]

Iím trying to build a ďLeave, Attendance ManagementĒ system using ExcelĒ. I am about 40% there so far.

I have a few problems to tackle which I will post in separate treads and link them back to together; so others can hopefully learn from the help I receive (and my mistakes):

The basic structure of the workbook is as follows:

12x main monthly ĎLeave Attendance Recordí worksheets (i.emonths April 2012 through to March 2013). [I will refer to these sheets as LARSheet1 to LARSheet12].
1 x worksheet which holds Ďdata validationí list andvlook up info. [ďFormulaListSheetĒ]
1 x worksheet acting as a database of staff details andpay numbers. [ďStaffdBaseSheetĒ]
1 x summary page which shows staff reaming leaveallocation. [ďSummaryLeaveSheetĒ]

What Iím having problems with at the moment is as follows:

A formula or a macro which works out how many days are present in a given month [starting date of month located in cell B10].
Days 1-27 will be already set up on the sheet. Based upon the monthly value I would like a solution which can calculate how many days are in a month and create a column for days 28, 29, 30 or 31 (depending on the month).

Hopefully I would like it to work like this:

When the user opens a new worksheet, they first entre the month start date in CELL B10. Hopefully this will trigger columns E13:AH22 to update with the relevant week day & date.

I also need the formulas and formatting in cells AH13:AH100 (column for day 27) to copied over automatically.

Can anyone help? Cheers moshjosh!

Sample of sheet setup:


====== ====== ======== ====== ============= ========== ============ ============ ===== ========= =======

This is a multi-part project which is collectively known as ďLeave, Attendance ManagementĒ system using ExcelĒ . Other parts are found on other treads. I hope to link the thread solutions together so others who wish to do a similar task can hopefully learn from help I receive (and my mistakes).

The other parts of the system are:

ĎLeave attendance recordí (this thread) worksheets (i.e months April 2012 through to March 2013). [I will refer to these sheets as "LARSheet1" to "LARSheet12" ].

ĎData validationí worksheet which holds list and vlook-up info for formulas. [I will refer to these sheet ďFormulaListSheetĒ ].

ĎDatabaseí worksheet acting as a of staff details database. [ ďStaffdBaseSheetĒ ]

ĎSummary reaming leaveí page which shows a summary of staff used leave and calculates reaming leave allocation. [ ďSummaryLeaveSheetĒ ]

Hello all, I am sure there is an easy answer for this, I just don't know it.

I have attached a spreadsheet that has 3 worksheets, summary and 2 individual. I am trying to drive the individual sheets from the summary page.

Basically, I enter the week ending and hours worked and the individual sheets would retain the accumulated individual info.
The formula I am using is as follows:
=IF(A5='Week End'!$B$2,'Week End'!$B$4,"")
What I have discovered is that as I change weeks the prior week become blank, just like I told it too!.
Is there a copy command I can use in a formula to copy or some other way to save the prior weeks info? I thought about Lookup but would run across the same problem.

Any help would be appreciated,

I am sure there is an easy fix for this and I just don't know what it is

I am simply trying to create a attendance record file. On the main sheet the week ending date is input and then a list of 75 employee names, input with Hrs, OT hrs blah blah. Then, I have created seperate worksheets for each respective employee that would maintain each week end total. I have been use the following formula:
IF(A5='Week End'!$B$2,'Week End'!$E$4,"") (see attached).

What I have found when the week end change the formula becomes "False" and the cell is empty. So I am losing my accumulation of prior weeks.

Is the "IF" statement the easiest for this


How do I save the prior weeks info.

I have attached a sample



I apologise in advance for being dim.

I need to copy certain columns from one sheet to another sheet based on a formlua on the date.

So, on 'Master Sheet' I have a column called 'Date'.

I would like a formula, or macro, which does the following.

If date (column D) is over 2 weeks old (today's date - 14 days) then copy columns A, D, S and T to another work sheet called 'Not Attended' and paste them into columns A,B,C, and D.

Is this possible?

Many thanks in advance.


Ok - I am not sure how easy this will be to explain. But I will do my best.

My girlfriend is a private music teacher. I am trying to help her get a bit more organized etc.

So - I created an Excel workbook to help her keep the kids info, the parent info and keep track of how much people owe, how much they paid, and the attendance of the kids in lessons.

On a second workbook inside the same file - I created an invoice. I have linked cells together to automate as many fields as possible. Now I would like to automate some of the calculations etc.

So - here is my problem. The attendace section is the most logical place to link the number of hours from. She actually tracks hours and it totals up at the end of the month.

But - is there a way to have the invoice sheet look at the date and pull in the hours from the dates belonging to the same month as the invoice? I guess you would have to reference the cell with the total amount of hours to the date cell - and then have the invoice sheet look at the invoice date - and reference over to the currect date in the 1st sheet.

Make sense? LOL

Ideally - I would like to make the invoice essentially generate itself from the info provided on the main workbook page.

Any help - would be much appreciated.

Hi all,

I am creating a attendance tracker. I need your help for the completion.

I am looking for a control which can track the changes. To be more specific, If a person updates the data in the sheet once, it should be locked. The next time, he/she should not be able to update it. It should be possible only with a controller who can in turn give a password and then make the necessary changes. I am not using any macros and it runs only on formulas. Please help me in this for the completion. I am already breaking my head for this.

Your help is appreciated,
Thanks in advance,

I have included a before/after example of what I am looking for. 7 columns total, excel needs to find the duplicates in column 1, delete duplicate based upon number in 5th column (keep the lowest number) and if possible make a seperate sheet of the ones that were duplicated, however that is the last thing I am worried about.

For the example, please see the original sheet and notice rows 19-23. It should keep row 22 and delete the other 4 rows with the same entry in column a.

If anyone can figrue this out, as a teacher at the school I attend cannot, you would greatly be respected, admired, and worshipped!


I have a class list in Excel.
About half of the class are seniors the others juniors. In the fist colomn there's a number(1-27) the second colonm is the rank(senior or junior) and the third colomn is the attendence for a single day(sept. 13) . What formula would i use to calculate how many seniors and how many juniors have attended on a certain date. I need these numbers separately, seniors in one cell and juniors in another.

Maybe there's a way to do it simpler in Access... any ideas?

Thank you in advance

Hey everyone, ive been reading through whilst developing my system and found the site very useful!
Trying to keep it concise,

I have

A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8).

N8 needs to count the number of "Present" values there are on another worksheet.
The other worksheet has dates across the top and names down the side.

When i use
=COUNTIF("Attendance!C9:Z9", "Present"),
and the next date comes along the formula changes to
=COUNTIF("Attendance!D9:AA9", "Present")

ie. the reference moves a column across - the new date's absent or present is not counted.

=COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present")
is no good because when i add a new name i need the row reference to move down as a row is inserted.

ie. both person's formulas count the same row.

So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system. Any ideas?


I need help with posting a If formula. Here is what I need.
C1 contains info for a class that is being scheduled and states if it has been attended or not. To calculate a percentage of completion rate I need C2 to show 100%. What formula should I use?

I wish to calculate numbers and/or cells w/numbers on a daily - monthly timeframe and then subtract those numbers as the previous year rolls into the next year Annually creating an annual attendance tracking log/card.

I have the "countif", "sum", etc. formulas, but I'm having trouble putting them together so that if for example, I missed this day last year, then today those points should roll off and if I missed this same day then these points should be added to the tracker.

Can anyone please help me with this?

I'm working on this, but figured it's much faster with many brains than just one:

There is a 6 day convention, with 240 people attending, & 45 tables that the people attending will be seated at. However, each day of the convention, each person sits at a new table, and each day they have to be seated with entirely new people - they aren't allowed to sit with anyone that they've already been at a table with on any previous day. Not all 45 tables have to be used.

Any thoughts? I've been testing out the formula used in this thread (Post #12):

posted by barry houdini & PGC.

My problem is figuring out how to adjust it, or if I should utilize something else! Thanks for any help!

Ok, I have the following code which sums up a lot of data into a summary sheet we send off to vendor's. The problem is I would like to not have any formulas on the summary sheet, but I cannot seem to figure out how to get an array formula as a value within VBA:

Sub test()
Dim counter As Long, LR As Long
counter = 5
LR = Sheets(2).Cells(Rows.Count, "D").End(xlUp).Row
    For b = 2 To LR
        If Application.WorksheetFunction.CountIf(Range("A:A"), Sheets(2).Range("D" & b).Value) = 0 Then
            Cells(counter, "A").Value = Sheets(2).Range("D" & b).Value
            Cells(counter, "C").FormulaArray = "=MIN(IF('" & Sheets(2).Name & "'!$D$2:$D$" & LR & "=A" & counter & ",'" &
Sheets(2).Name & "'!$M$2:$M$" & LR & "))"
            Cells(counter, "D").Value = Evaluate("{=MAX(IF('" & Sheets(2).Name & "'!$D$2:$D$" & LR & "=A" & counter & ",'" &
Sheets(2).Name & "'!$M$2:$M$" & LR & "))}")
            Cells(counter, "K").Value = Application.WorksheetFunction.SumIf(Sheets(2).Range("D:D"), Range("A" &
counter).Value, Sheets(2).Range("Z:Z"))
            Cells(counter, "M").Value = Sheets(2).Range("H" & b).Value
            Cells(counter, "N").Value = Sheets(2).Range("F" & b).Value
            counter = counter + 1
        End If
    Next b
End Sub
If I enter as a formula array which is the red code, it works fine and I get correct results, but there is a formula in the spreadsheet, now I know I could copy and paste values within the macro, but this has to be possible without having to do that? Or is it not possible. The blue code is what i thought I'd try, but it doesn't work.

Here is an example of what the summary sheet looks like even though seeing it probably doesn't help much:

Microsoft Excel - Attends Healthcare1 weeks data supplier test.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC5C6C7C8=
ABCDEFGHIJKLMN4xpedx LocationLocation IdentifierStart Date  (MM/DD/YY) - (include slashes)End Date  (MM/DD/YY) - (include slashes)Invoice date  (MM/DD/YY) - (include slashes)Date IssuedGross AmountDiscount AvailableNet AmountCredit Invoice Number Original Amount      Requested Invoice RemarksVendor NumberInternal             xpedx JDE #5Saalfeld NWG - Seattle 5/5/2008#VALUE!      $113.55 2837003976Metro New York Commercial 5/7/2008#VALUE!      $746.64 102357006007Harrisburg 5/5/2008#VALUE!      $9,790.54 102357006558Harrisburg Redistribution 5/9/2008#VALUE!      $839.74 10235700659Credit Summary Sheet 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

No luck finding an answer? You could always try Google.