The Animal
Active Member
- Joined
- May 26, 2011
- Messages
- 449
Hi
I am creating a "Staff Roster" checklist
In cells J5:J11 I have a set value used as the budget number of staff to work that day.
In G3:33 I have a list of staff names
In J13:33 I enter the task required oppposite that employee which is A, B, C etc, A= Shift manager B=Driver C= Dock etc
In J34:40 I have a formula that counts the number of A's, B's etc and then compares that total to the corresponding budget number in J5:11.
As I add the task letter next to the staff names the cells in 34:40 displays a count showing if I am over or under Budget number.
This is my problem. The normal working week for an employee is 5 days but they may do an extra which I use the letter "X" as the nominate.
If i use code =SUM(COUNTIF(J$13:J$33,{"A","X"}))-J5 in J34 which compares to the value in J5 and I use un X in that column instead of a "A" which is the shift manager letter that cell calculation is fine BUT as I use "X" for all additional working days then all the other calculation cells count the "X" for all codes.
I am adding "X" to all codes as below,
=SUM(COUNTIFS(J$13:J$33,{"B","X"}))-J6
=SUM(COUNTIF(J$13:J$33,{"C","X"}))-J7
Etc
I know why its doing it as its counting "X" in all employee types in J13:33 but dont know how to fix it
Any help would be great
Thanks Stephen
I am creating a "Staff Roster" checklist
In cells J5:J11 I have a set value used as the budget number of staff to work that day.
In G3:33 I have a list of staff names
In J13:33 I enter the task required oppposite that employee which is A, B, C etc, A= Shift manager B=Driver C= Dock etc
In J34:40 I have a formula that counts the number of A's, B's etc and then compares that total to the corresponding budget number in J5:11.
As I add the task letter next to the staff names the cells in 34:40 displays a count showing if I am over or under Budget number.
This is my problem. The normal working week for an employee is 5 days but they may do an extra which I use the letter "X" as the nominate.
If i use code =SUM(COUNTIF(J$13:J$33,{"A","X"}))-J5 in J34 which compares to the value in J5 and I use un X in that column instead of a "A" which is the shift manager letter that cell calculation is fine BUT as I use "X" for all additional working days then all the other calculation cells count the "X" for all codes.
I am adding "X" to all codes as below,
=SUM(COUNTIFS(J$13:J$33,{"B","X"}))-J6
=SUM(COUNTIF(J$13:J$33,{"C","X"}))-J7
Etc
I know why its doing it as its counting "X" in all employee types in J13:33 but dont know how to fix it
Any help would be great
Thanks Stephen
