Attendance Sheet problem

[majidsiddique]

hi all,
i am making a attendance sheet month wise.
AG=COUNTIF($B3:$AF3,"p")
AH=COUNTIF($B3:$AF3,"L")
i want, if L more then 3 days Leave then p automatic replace with L fro given range.
cell color must be change in RED color only for this condition apply.
Khalid and nasir is example of this.
Nasir p replace 2
kahlid p replace 1

thanks

Attendance Sheet 1-12-2018

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

Total-P

Total-L

majid

p

p

p

p

p

p

L

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

30

1

sajid

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

31

0

khalid

p

p

L

L

p

p

p

p

L

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

L

p

p

p

27

4

ahmed

p

p

p

p

p

p

p

p

p

p

p

p

L

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

p

L

29

2

Nasir

p

p

p

p

p

p

p

L

L

p

p

p

L

p

p

p

p

p

p

L

p

p

p

p

p

p

p

p

p

p

L

26

5

<colgroup><col><col span="9"><col span="22"><col><col></colgroup><tbody>
</tbody>

 

[majidsiddique]

Yes MICKG,
simple countif formula cannot do it if apply direct on it.
please do it as you mention for calculation, BUT please change RED P background color RED.
one thing remind, when i open this file, coding must be working always if any change in save a file.

Thanks a lot.

 

[MickG]

Try this:-
https://app.box.com/s/s0e0h30b2g8jttu03an8kkjyn1nefwh2

 

[majidsiddique]

Thanks MickG
very well done, great job,
i love if possible by formula.
again thanks.

 

[MickG]

You're welcome