Having troubles with this Excel formula?

Let's say that the names are in column "A", the status is in "B", and "C" was the column where the managers can put a "1". Let's also say that you're using rows 1 through 50.

Then in D1, enter "=+IF(AND(B2="part time",C2=1),1,0)" without the quotes. Copy this same cell into D2 through D50. This puts a "1" at the end of the row if a worker is part time and has perfect attendance, and "0" otherwise. Now sum column D.

The problem is the "if" in countif or sumif only does one test and can't test each employee seperately.

For multiple cretiria, use

COUNTIFS (with an "s")

Lets suppose column a = employee and you have 10 employees

column b = 1 for full time and 0 = for part time

column c = 1 for perfect attendance and 0 for less than perfect.

Your formula would look like this

=COUNTIFS(B1:B10,0,C1:C10,1)

This will let you calculate without adding a bunch of extra columns you don't really need or want.

Say column B is whether they are part time, and column C is the 1 or 0 for perfect attendance.

Set another column to have the equation =if(B1="part time",1,0)

That will set the cell equal to 1 if they are part time. Drag that equation down to have the entire list be considered by the equations.

Set another column to have the equation = if(C1=1,1,0)

That will set that cell value to 1 if they have perfect attendance, and 0 if they don't.

Then in another column, simply multiply the other 2 columns you created, thus making both have to be 1 for a 1 to appear in that column. Then sum the column to find how many people have perfect attendance and are part time.

Re the 80 hrs state of affairs: in case you multiply 80 hours by 20% and then subtract from the unique 80 hours, you're left with 80% of 80 hours. SO your formula ought to mmore only be 80 hours * 80%. HTH!