# Attendance Excel Formula

1. ## Attendance Excel Formula

This is somewhat related to the previous thread I posted before but this one is kinda complicated (I guess, for me). P is Present and L is Late. I know how to use the COUNTIF function, but I want to know if what I want is possible. It's like answering 1+1+2+1=1 or vice versa. I want to be able to have an answer of 1 even though the person was present 3 times or has been late once. Please help me.

In Joe Smith's row, P is the only letter being counted. =IFERROR(COUNTIF(B2:F2,"P")/COUNTIF(B2:F2,"P"),0)
In Earl Root's row, L is the one being counted. =IFERROR(COUNTIF(B3:F3,"L")/COUNTIF(B3:F3,"L"),0)

What I want is, in all rows, I want to be able to count both P and L but the Total or the result is still 1

It's like =IFERROR(COUNTIF(B2:F2,"P")/COUNTIF(B2:F2,"P"),0) and =IFERROR(COUNTIF(B3:F3,"L")/COUNTIF(B3:F3,"L"),0) all together. If i'm doing this wrong, please let me know. http://i60.tinypic.com/11gua6c.png  Register To Reply

2. ## Re: Attendance Excel Formula

exactly what output should you want  Register To Reply

3. ## Re: Attendance Excel Formula Originally Posted by Naveed Raza exactly what output should you want
I want to count both P and L in a week but the Sum or Total is still "1"  Register To Reply

4. ## Re: Attendance Excel Formula

Hi

In G2 Cell paste below function and drag down ``Please Login or Register  to view this content.``  Register To Reply

5. ## Re: Attendance Excel Formula

What is your logic behind getting 1 in col. G for each row? or what are you trying to count to get 1 in col. G? Would you please explain it again?
Should it return 1 in col. G if present at least once in a week or what?  Register To Reply

6. ## Re: Attendance Excel Formula

U can try this 1
=if(or(countif(b2:f2,"p")=5,countif(b2:f2,"l")=5),1,if(or(countif(b2:f2,"p")=4,countif(b2:f2,"l")=4),1,if(or(countif(b2:f2,"p")=3,countif(b2:f2,"l")=3),1)))  Register To Reply

7. ## Re: Attendance Excel Formula

I don't get the logic but still try this

Formula:  `Please Login or Register  to view this content.`  Register To Reply

8. ## Re: Attendance Excel Formula Originally Posted by sktneer What is your logic behind getting 1 in col. G for each row? or what are you trying to count to get 1 in col. G? Would you please explain it again?
Should it return 1 in col. G if present at least once in a week or what?
Yes. It should return 1 in column G if present AND OR absent at least once in a week. I want to be able to count the total number of students present in a week.  Register To Reply

9. ## Re: Attendance Excel Formula Originally Posted by Naveed Raza Hi

In G2 Cell paste below function and drag down ``Please Login or Register  to view this content.``
It's not counting anything when i tried pasting it  Register To Reply

10. ## Re: Attendance Excel Formula Originally Posted by rishiraj46 U can try this 1
=if(or(countif(b2:f2,"p")=5,countif(b2:f2,"l")=5),1,if(or(countif(b2:f2,"p")=4,countif(b2:f2,"l")=4),1,if(or(countif(b2:f2,"p")=3,countif(b2:f2,"l")=3),1)))

I tried this but it's showing FALSE  Register To Reply

11. ## Re: Attendance Excel Formula Originally Posted by boopathiraja I don't get the logic but still try this

Formula:  `Please Login or Register  to view this content.`

Thanks, but it only counts the P I want the return in column G as 1 if i type P and or L  Register To Reply

12. ## Re: Attendance Excel Formula

There are two cases what I see.

1) If a student is present at least once in the week. Try this.... ``Please Login or Register  to view this content.``
2) If a student is present at least once and absent at least once in a week. Try this.... ``Please Login or Register  to view this content.``
Both the formula will return 1 in col. G if the condition is true otherwise a 0.
Which of these two cases suits your requirement?  Register To Reply

13. ## Re: Attendance Excel Formula Originally Posted by sktneer There are two cases what I see.

1) If a student is present at least once in the week. Try this.... ``Please Login or Register  to view this content.``
2) If a student is present at least once and absent at least once in a week. Try this.... ``Please Login or Register  to view this content.``
Both the formula will return 1 in col. G if the condition is true otherwise a 0.
Which of these two cases suits your requirement?
This only counts the P. I want it like if I type just L, the return would still be 1 same return if I type just P.  Register To Reply

14. ## Re: Attendance Excel Formula

Formula:  `Please Login or Register  to view this content.`  Register To Reply

15. ## Re: Attendance Excel Formula Originally Posted by akosibhon This only counts the P. I want it like if I type just L, the return would still be 1 same return if I type just P. ``Please Login or Register  to view this content.``  Register To Reply

16. ## Re: Attendance Excel Formula Originally Posted by sktneer  ``Please Login or Register  to view this content.``
Yay! It worked!!! Thank you so much, sir! I really appreciate all your help!    Register To Reply

17. ## Re: Attendance Excel Formula

Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.   Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 