+ Reply to Thread
Results 1 to 17 of 17

Attendance Excel Formula

  1. #1
    Registered User
    Join Date
    06-19-2014
    Location
    Manila, Philippines
    MS-Off Ver
    Office for Mac
    Posts
    11

    Attendance Excel Formula

    I am trying to find out how I can go about this. Please refer to the attached image.

    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
    Attached Images Attached Images
    Last edited by akosibhon; 09-25-2014 at 01:06 AM.

  2. #2
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Attendance Excel Formula

    exactly what output should you want
    Thanks - Naveed
    -----------------------------
    If the suggestion helps you, then Click * to Add Reputation
    To Attach File: Go Advanced>>Manage Attachments>>Add Files (In Top Right Corner)>>SelectFiles>>.........Locate Your File(s)>>Upload Files>>Done (In Bottom Right)
    1. Use [code] code tags [\code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

  3. #3
    Registered User
    Join Date
    06-19-2014
    Location
    Manila, Philippines
    MS-Off Ver
    Office for Mac
    Posts
    11

    Re: Attendance Excel Formula

    Quote Originally Posted by Naveed Raza View Post
    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. #4
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Attendance Excel Formula

    Hi

    In G2 Cell paste below function and drag down

    Please Login or Register  to view this content.

  5. #5
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    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?
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  6. #6
    Registered User
    Join Date
    09-09-2014
    Location
    Pune, India
    MS-Off Ver
    2010
    Posts
    18

    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. #7
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Attendance Excel Formula

    I don't get the logic but still try this

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Click just below left if it helps, Booşathì

  8. #8
    Registered User
    Join Date
    06-19-2014
    Location
    Manila, Philippines
    MS-Off Ver
    Office for Mac
    Posts
    11

    Re: Attendance Excel Formula

    Quote Originally Posted by sktneer View Post
    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. #9
    Registered User
    Join Date
    06-19-2014
    Location
    Manila, Philippines
    MS-Off Ver
    Office for Mac
    Posts
    11

    Re: Attendance Excel Formula

    Quote Originally Posted by Naveed Raza View Post
    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. #10
    Registered User
    Join Date
    06-19-2014
    Location
    Manila, Philippines
    MS-Off Ver
    Office for Mac
    Posts
    11

    Re: Attendance Excel Formula

    Quote Originally Posted by rishiraj46 View Post
    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. #11
    Registered User
    Join Date
    06-19-2014
    Location
    Manila, Philippines
    MS-Off Ver
    Office for Mac
    Posts
    11

    Re: Attendance Excel Formula

    Quote Originally Posted by boopathiraja View Post
    I don't get the logic but still try this

    Formula: copy to clipboard
    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. #12
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    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. #13
    Registered User
    Join Date
    06-19-2014
    Location
    Manila, Philippines
    MS-Off Ver
    Office for Mac
    Posts
    11

    Re: Attendance Excel Formula

    Quote Originally Posted by sktneer View Post
    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. #14
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Attendance Excel Formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  15. #15
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Attendance Excel Formula

    Quote Originally Posted by akosibhon View Post
    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.
    What about this?
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    06-19-2014
    Location
    Manila, Philippines
    MS-Off Ver
    Office for Mac
    Posts
    11

    Re: Attendance Excel Formula

    Quote Originally Posted by sktneer View Post
    What about this?
    Please Login or Register  to view this content.
    Yay! It worked!!! Thank you so much, sir! I really appreciate all your help!

  17. #17
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Attendance Excel Formula

    Glad I could help.
    If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 10
    Last Post: 03-17-2016, 05:34 AM
  2. SUMIFS Formula Help - Attendance sum per month
    By jpoppet in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-06-2013, 10:13 AM
  3. Attendance tracking - Formula based
    By yogananda.muthaiah in forum Excel General
    Replies: 2
    Last Post: 04-27-2012, 09:34 PM
  4. Excel 2007 : Attendance formula
    By chefpoofoo in forum Excel General
    Replies: 7
    Last Post: 07-03-2011, 06:16 PM
  5. Replies: 3
    Last Post: 06-12-2011, 02:56 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1