Free Microsoft Excel 2013 Quick Reference

# Count for present absent days Results

## Count for present & absent days

I use Excel 2000
I have sheet muster for my clients of January, 2005 like :
( P = Present, A=Absent )
A.....B.....C.....D.....
Days Sun Mon Tue Wed
Date 1 2 3 4

1 John P A A P
2 Lucy A P P A
3
Now I want to calculate the total Sundays, Mondays, Tuesdays etc. present in
that month. How can I do this?
--
Knowldege is Power

## Count Consecutive Values in Cells

I would like to do an attendance sheet. I would like to use 1 & 0 for present(=1) & absent(=0). I want to find out if a student has been absent for 4 consecutive days and if there is 4 consecutive 0 then the formula should return the value “Too many absences” into a cell next to the name.

1 1 1 0 0 111 1 0 = “Reached 4 Consecutive Absences” in cell next to name in row.

10 01 0 1 1 01 0 = 0

The purpose is to find out when a person hit 4 absences.
Is there a way to do it with a “P” and an “A” for present and absent?

I tried these two, they both did not work:

=IF(MAX(FREQUENCY(IF(A1:I1=0,ROW(A1:I1)),IF(A1:I10,ROW(A1:I1))))>=3,0,SUM(A1:I1))
=IF(MAX(FREQUENCY(IF(A1:I1=0,COLUMN(A1:I1)),IF(A1:I10,COLUMN(A1:I1))))>=3,0,SUM(A1:I1))

I also did the Ctrl+Shift+Enter

## Count Consecutive Days

Hi all,

I have a workbook to record the attendance of students. Sheet attendance9 for September's attendance. Enter 1 for present, 0 for absent, L for late, E for early leave and blank cell for no schoolday.
Sheet "remark9" is used to record detail of attendance for September.
In sheet "attendance9", I want to display in cell AT2 the number of times a student absent for 7 or 14 consecutive days.
How to count from E2:AH2?

Thanks all

## Count Consecutive Numbers

I am preparing a attendance sheet. I am using 1 & 0 for present(=1) & absent(=0). I want to find out if a student has been absent for three consecutive days and if there is three consecutive 0 then the formula should return the value 0 ( the student gets 0 if he is absent for 3 consecutive days ) otherwise it should add all the 1s in the row. i.e

1 1 1 0 0 1 1 0 = 5

10 0 0 1 1 0 0 = 0

PLZZZ HELP I HAVE TO SUBMIT MY ASSIGNMENT ASAP.

THANXX in advance.

## Count Numbers Based On Criteria

Dear Helper,

I attach a book2.xls. In this book, I want a formula in Sheet1!J2 to calculate the number of late period for the 7 days. Time table is in Sheet2. The rule is:

1. Enter 1 means present for all periods for that day, no. of late period is 0;
2. 0 means absent for all periods for that day, no. of late period is 12;
3. E means early leave. Time will be recorded in Sheet3;
3. If the student present in the 3rd period, the number of late period will be 2.

Thanks for your help.

## I have 3 criteria to count from which 1 is changing

i have to create daily report in which present ,absent, night shift people to
be counted i am using sumproduct but for another day nightshift changes den
previous days report changes please help me

## Count Based on Conditions or Criteria

Hello all,

I have an attend workbook to calculate the attendance of student. Sheet attendance9 represent

Sept...etc.

Enter 1 means present, 0 means absent, L means late, E means early leave.

In Sheet "statistics_by_day", when user enter class, month and day in A2:C2, number of students late

and early leave will be displayed in D7 and E7. How to write formula in D7, E7 to locate the

corresponding day of corresponding worksheet and count the number of students late and early leave for

the class?

## Counting presence/absence data within an array

Hi
I am trying to lookup and count presence/absence that is within a day and hour array. For example, I have an array with this data:
A1 (animal ID): 1-63
B1 (day): range 83-243
C1 (hour): range 0-23
D1 (P/A): 0 is absent, >= 1 is present

Then I want to count the number of presents (H1) and the total number of P/A surveys (I1) within a series of rows that matches a set of values. For example, for animal 47 (E1: 47) within the days ranging from 121-181 (F1: 121, 122, …181) at hour 3 (G1:3), how many surveys was the animal present (H1) and how many total surveys were conducted (I1)?

E1 (specific animal ID): 47
F1 (specific day range): 121-181
G1 (specific hour): 3
H1 (count of D1 >= 1):
I1 (count of all D1 values):

This formula is beyond me!

Thanks

## One more on PivotTable

I have Table with days of the week in the columns heading and list of people in the rows heading. The data shows whether a particular person was present or absent on a given day.

Now I need to find the count of presents for each weekday. Simple solution is countif.

Question is whether I can plot these on a PivotTable? How?

(I will be adding a sample sheet as soon as I figure out how to do it)

## Attendance Tracker

Hi,

I have attached a sample tracker - where i have 2 tabs one is attendance tracker and another one is production tracker.

Now i am looking out for a formula which counts attendance based on there production numbers for that respective day.

Here is what is am looking:

If an employee has done production more than "0" than his attendance should reflect as "Present".

If an employee has done production less than or equal to "0", than his attendance should reflect as "Absent".

Can anyone please help me out to solve this.

## Stop Formula Column Reference Changing On Insert But Not Row Reference

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.

Using
=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?

Thanks

## Refining Design Of Spreadsheet

Split from here

Sho - that is a great way of creating a date range and I feel will come in very useful when we get to the stage of autogenerating a detention date range for a specific pupil in the database. Thanks

The reports can have nothing other than information that is either in the database or derived from the database. Think of it as a Report On The Database Contents. So, somehow, the information about the time served has to be in the database. You can gather information on the piece of paper you call the roll from (a checkmark if they are present) for later entering into the database but, you wouldn't actually put information into the report spreadsheet. You would take the roll and add a record in the database that says the student served detention on a certain day for a certain offense marked with the "Served" option in the dropdown list. Jim,the idea was to produce a paperless system - an integrated way of recording who was there is the way I want to go - any way to filter the database so it only displays pupils on the required date and use the database itself as the method of recording who was present?

The number of detentions to be served is a calculation (I think) based on the kind of detention. Can you post the formula so I can understand better? Yes - there is a 'scoring system' based on the type of offence:

One late = 1 detention. Theoretically, a pupil can be late twice in a day - one for the morning session (am) and another for the afternoon session (pm), so

am=1, pm=1, am + pm =2

Truancy is more severe. This is the table we use for the number of detentions to lessons missed

1 lesson = 2
2 lessons = 4
3 lessons = 6
4 lessons = 8
5 & 6 lessons = 10

Disruption means that the pupil does the detention again so Disruption = detention count remains the same. Disruption is basically talking !

I like your analogy to a sales tracking system. The students are buying time when they commit an offense and paying it off in installments. I wonder if there is interest for missing a payment? Upside? Hmm I will find that one out for you - I think if a pupil deliberately skips a detention they get placed in the Headteacher's Detention on Friday (an hour long whammy). If a pupil is genuinely absent s/he is booked into the next day's detention. Will look through your file after I post this.

## Another question about IF(COUNTIF) checks in Excel

> wrote in message
> ...
> > Hey again,
> >
> > Is there a formula I can use to figure out if there is one specific phrase
> > in a selection?
> >
> > I'm updating the attendance prgram at the school that I work at. Every
> > student has their own attendance sheet in Excel, and each sheet sort of
> > looks
> > like this:
> >
> > |Monday|
> > Period 1:| A | (A=Absent; S=Seat time)
> > Period 2:| S |
> > Period 3:| A |
> >
> > So basically, if they have one or more "S"s, we can count them as being
> > present for the day. I need the formula to find out if there is an "S" in
> > that column, and if there is, I need it to type an "S" in the attendance
> > report, which sort of looks like this:
> > DATE: |1|2|3|
> > Student Name |S| | |
> >
> > Sorry if that got a little complicated. Is there any way to do this?

Oh, one more question. This is rediculously complicated, I know, but... when
you use the IF(COUNTIF) formula, can you put another IF(COUNTIF) check in
the "value if false" spot? Because if there isn't an "S" in the column, it
needs to be able to check for a "P" or an "N" (independent study or not
enrolled) and mark
that instead.

I've been trying to figure it out with the =OR formula but I keep getting
errors. This is what I've been typing that's not working and I don't know why:

=OR(IF(COUNTIF(AA8:AA10,"S"),"S",(IF(COUNTIF(AA8:A A10,"P"),"P","A"))))

Any ideas?

## Can anybody help me with basic sum formula please?

Hi Everyone,

I am having really bad trouble with an excel spreadsheet made for one
of our guys to monitor the sales in our company (selling phones).

Basically we have a workbook that has all stuff like Pass, Fail, and
Pendings etc for each sales person split up by days. Then we have
formula at the end which adds up a summary of the weeks figures. The
only thing,the guy puts in H (for holiday) or S (for sickness) in the
cells if the sales person is absent, but this then screws up the
formula and we cant see the totals! We need something that will add up
the relevant cells but that counts a "H" or "S" as a "0" if it is
present in the cell.

The existing formula is just

=SUM(E3+I3+M3+Q3+U3+Y3)

obviously in this there is nothing to say count H or S as zero.

Another person has suggested something like the formula below, however
we can't get this to work either.

=IF(OR(E3,I3,M3,Q3,U3,Y3="H"),0,IF(OR(E3,I3,M3,Q3, U3,Y3="S"),0,""))+(E3+I3+M3+Q3+U3+Y3)

If anybody can help us out we would be eternally gratefull. I can't
stress how much this would please us if somebody knew the answer or
correct formula for this problem.

I have uploaded the actual form itself if anyone wants to take a look
at it.
It is here

http://s65.yousendit.com/d.aspx?id=1...M230PKIKYDLSK3

Thanks for taking your time to read this and i really appreciate any
help.

-Andy

--
andrewsnaith
------------------------------------------------------------------------
andrewsnaith's Profile: http://www.excelforum.com/member.php...o&userid=32110
View this thread: http://www.excelforum.com/showthread...hreadid=518673

## Complex Summing probably using Match at some point...

Hi, I'm hoping someone from this group can help me out with this problem.
It's a temporary solution whilst I write some macros so that the process is
better but I need a "quick-fix" which I'm hoping I'll be able to get by using
some magic combination of formulas.

I have attached a workbook with dummy data in that will paint the picture
better than I can in words although here's a brief description of the
problem. I have a sheet that we use to track what project people are
allocated on. Each day we create a new workbook and each workbook will
contain each project on a separate sheet. There are three groups of employees
on each project (for sub departments).

I'd like to work out a formula that will react to row insertions (and
deletions) that counts up the people who are present and absent for each
group. It would also need to look at those people who are marked as "Exclude"
(see workbook).

The current formula I'm using is:
=COUNTIF(G14:G39,"YES")-SUMPRODUCT((G14:G39="YES")*(M14:M39="Exclude"))
(some cell refs are wrong there...the dummy allocation attached has
different cols)

What I'm after is some way of changing the "G39" reference so that it looks
for the next cell with "PRESENT" in it after a particular cell. In this way
it should react to changes in the document.

I'm thinking that I could use some combination of the SUMPRODUCT I already
use and the MATCH formula (or FIND perhaps?) to correctly determine the right
range for the appropriate group. Of course the last group should be easy as I
can just go from the bottom :-)

As I said I am writing some macros to tidy this application up but until
then does anyone have any suitable suggestions for sorting this out?

Thanks
George

## Is there a formula to find one phrase in a selection?

Hey again,

Is there a formula I can use to figure out if there is one specific phrase
in a selection?

I'm updating the attendance prgram at the school that I work at. Every
student has their own attendance sheet in Excel, and each sheet sort of looks
like this:

|Monday|
Period 1:| A | (A=Absent; S=Seat time)
Period 2:| S |
Period 3:| A |

So basically, if they have one or more "S"s, we can count them as being
present for the day. I need the formula to find out if there is an "S" in
that column, and if there is, I need it to type an "S" in the attendance
report, which sort of looks like this:
DATE: |1|2|3|
Student Name |S| | |

Sorry if that got a little complicated. Is there any way to do this?

## Can anybody help me with basic sum formula please?

Hi Everyone,

I am having really bad trouble with an excel spreadsheet made for one of our guys to monitor the sales in our company (selling phones).

Basically we have a workbook that has all stuff like Pass, Fail, and Pendings etc for each sales person split up by days. Then we have formula at the end which adds up a summary of the weeks figures. The only thing,the guy puts in H (for holiday) or S (for sickness) in the cells if the sales person is absent, but this then screws up the formula and we cant see the totals! We need something that will add up the relevant cells but that counts a "H" or "S" as a "0" if it is present in the cell.

The existing formula is just

=SUM(E3+I3+M3+Q3+U3+Y3)

obviously in this there is nothing to say count H or S as zero.

Another person has suggested something like the formula below, however we can't get this to work either.

=IF(OR(E3,I3,M3,Q3,U3,Y3="H"),0,IF(OR(E3,I3,M3,Q3,U3,Y3="S"),0,""))+(E3+I3+M3+Q3+U3+Y3)

If anybody can help us out we would be eternally gratefull. I can't stress how much this would please us if somebody knew the answer or correct formula for this problem.

I have uploaded the actual form itself if anyone wants to take a look at it.
It is here

http://s65.yousendit.com/d.aspx?id=1...M230PKIKYDLSK3

Thanks for taking your time to read this and i really appreciate any help.

-Andy

## Another question about IF(COUNTIF) checks in Excel

<Tiff1618@discussions.microsoft.com> wrote in message
> news:219EB429-F90A-406E-A208-5C787ED70467@microsoft.com...
> > Hey again,
> >
> > Is there a formula I can use to figure out if there is one specific phrase
> > in a selection?
> >
> > I'm updating the attendance prgram at the school that I work at. Every
> > student has their own attendance sheet in Excel, and each sheet sort of
> > looks
> > like this:
> >
> > |Monday|
> > Period 1:| A | (A=Absent; S=Seat time)
> > Period 2:| S |
> > Period 3:| A |
> >
> > So basically, if they have one or more "S"s, we can count them as being
> > present for the day. I need the formula to find out if there is an "S" in
> > that column, and if there is, I need it to type an "S" in the attendance
> > report, which sort of looks like this:
> > DATE: |1|2|3|
> > Student Name |S| | |
> >
> > Sorry if that got a little complicated. Is there any way to do this?

Oh, one more question. This is rediculously complicated, I know, but... when
you use the IF(COUNTIF) formula, can you put another IF(COUNTIF) check in
the "value if false" spot? Because if there isn't an "S" in the column, it
needs to be able to check for a "P" or an "N" (independent study or not
enrolled) and mark
that instead.

I've been trying to figure it out with the =OR formula but I keep getting
errors. This is what I've been typing that's not working and I don't know why:

=OR(IF(COUNTIF(AA8:AA10,"S"),"S",(IF(COUNTIF(AA8:AA10,"P"),"P","A"))))

Any ideas?

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