# 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

2. ## Re: Attendance Excel Formula

exactly what output should you want

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"

4. ## Re: Attendance Excel Formula

Hi

In G2 Cell paste below function and drag down

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

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?

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

7. ## Re: Attendance Excel Formula

I don't get the logic but still try this

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

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.

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

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

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

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?

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.

14. ## Re: Attendance Excel Formula

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

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.``

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!

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.

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