Free Microsoft Excel 2013 Quick Reference

- Count for present & absent days
- Count Consecutive Values in Cells
- Count Consecutive Days
- Count Consecutive Numbers
- Count Numbers Based On Criteria
- I have 3 criteria to count from which 1 is changing
- Count Based on Conditions or Criteria
- Counting presence/absence data within an array
- One more on PivotTable
- Attendance Tracker
- Stop Formula Column Reference Changing On Insert But Not Row Reference
- Refining Design Of Spreadsheet
- Another question about IF(COUNTIF) checks in Excel
- Can anybody help me with basic sum formula please?
- Complex Summing probably using Match at some point...
- Is there a formula to find one phrase in a selection?
- Can anybody help me with basic sum formula please?
- Another question about IF(COUNTIF) checks in Excel

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

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

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

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.

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.

be counted i am using sumproduct but for another day nightshift changes den

previous days report changes please help me

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?

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

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)

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.

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

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.

> ...

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

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

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

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

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