+ Reply to Thread
Results 1 to 9 of 9

Count for present & absent days

  1. #1
    Ajit Munj
    Guest

    Count for present & absent days

    I use Excel 2000
    I have sheet muster for my clients of January, 2005 like :
    ( P = Present, A=Absent )
    A.....B.....C.....D.....
    Days Sun Mon Tue Wed
    Date 1 2 3 4

    1 John P A A P
    2 Lucy A P P A
    3
    Now I want to calculate the total Sundays, Mondays, Tuesdays etc. present in
    that month. How can I do this?
    --
    Knowldege is Power

  2. #2
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    Your query is not clear. To count the number of 'p's, just use
    =COUNTIF(B1:E1,"p")
    this is considering that your data for john lies in range B1 to E2
    similarly for 'a's
    =COUNTIF(B1:E1,"a")

    If there are any blanks as on holidays, then you can count blanks using
    =COUNTBLANK(B1:E1)

    The total of all the above 3 should add up to your total days.


    - Mangesh

  3. #3
    Jason Morin
    Guest

    Re: Count for present & absent days

    This should get you started. To count the total # of P's
    for John for January (assuming your data below is just
    for January), try:

    =SUMPRODUCT((3:3="P")*(1:1="Sun"))

    where John is listed on row 3 and the Days are listed in
    row 1.

    HTH
    Jason
    Atlanta, GA

    >-----Original Message-----
    >I use Excel 2000
    >I have sheet muster for my clients of January, 2005

    like :
    >( P = Present, A=Absent )
    > A.....B.....C.....D.....
    >Days Sun Mon Tue Wed
    >Date 1 2 3 4
    >
    >1 John P A A P
    >2 Lucy A P P A
    >3
    >Now I want to calculate the total Sundays, Mondays,

    Tuesdays etc. present in
    >that month. How can I do this?
    >--
    >Knowldege is Power
    >.
    >


  4. #4
    Ajit Munj
    Guest

    Re: Count for present & absent days & conversion of actual fig.

    Thanks Jason,It's working.
    I also want to convert actual numbers (a1=46556.4646, a2=787979.79798 etc.)
    into lacs (i.e. a1=0.465564646, a2=7.87979.79798 etc.) at one go and also one
    by one. How can I do this? I also want Excel to prompt me for convesion in
    Lacs or Crores or Thousands? Pl. guide.

    Ajit
    "Jason Morin" wrote:

    > This should get you started. To count the total # of P's
    > for John for January (assuming your data below is just
    > for January), try:
    >
    > =SUMPRODUCT((3:3="P")*(1:1="Sun"))
    >
    > where John is listed on row 3 and the Days are listed in
    > row 1.
    >
    > HTH
    > Jason
    > Atlanta, GA
    >
    > >-----Original Message-----
    > >I use Excel 2000
    > >I have sheet muster for my clients of January, 2005

    > like :
    > >( P = Present, A=Absent )
    > > A.....B.....C.....D.....
    > >Days Sun Mon Tue Wed
    > >Date 1 2 3 4
    > >
    > >1 John P A A P
    > >2 Lucy A P P A
    > >3
    > >Now I want to calculate the total Sundays, Mondays,

    > Tuesdays etc. present in
    > >that month. How can I do this?
    > >--
    > >Knowldege is Power
    > >.
    > >

    >


  5. #5
    Jason Morin
    Guest

    Re: Count for present & absent days & conversion of actual fig.

    You divide cells one by one with a formula in another
    cell:

    =A1/100000

    or to do all of them at once, put 100000 in an open cell,
    copy it, select your numbers in column A, and go to Edit
    > Paste Special > Divide.


    HTH
    Jason
    Atlanta, Ga

    >-----Original Message-----
    >Thanks Jason,It's working.
    >I also want to convert actual numbers (a1=46556.4646,

    a2=787979.79798 etc.)
    >into lacs (i.e. a1=0.465564646, a2=7.87979.79798 etc.)

    at one go and also one
    >by one. How can I do this? I also want Excel to prompt

    me for convesion in
    >Lacs or Crores or Thousands? Pl. guide.
    >
    >Ajit
    >"Jason Morin" wrote:
    >
    >> This should get you started. To count the total # of

    P's
    >> for John for January (assuming your data below is just
    >> for January), try:
    >>
    >> =SUMPRODUCT((3:3="P")*(1:1="Sun"))
    >>
    >> where John is listed on row 3 and the Days are listed

    in
    >> row 1.
    >>
    >> HTH
    >> Jason
    >> Atlanta, GA
    >>
    >> >-----Original Message-----
    >> >I use Excel 2000
    >> >I have sheet muster for my clients of January, 2005

    >> like :
    >> >( P = Present, A=Absent )
    >> > A.....B.....C.....D.....
    >> >Days Sun Mon Tue Wed
    >> >Date 1 2 3 4
    >> >
    >> >1 John P A A P
    >> >2 Lucy A P P A
    >> >3
    >> >Now I want to calculate the total Sundays, Mondays,

    >> Tuesdays etc. present in
    >> >that month. How can I do this?
    >> >--
    >> >Knowldege is Power
    >> >.
    >> >

    >>

    >.
    >


  6. #6
    Jim May
    Guest

    Re: Count for present & absent days

    You can also use the = countif() function
    For Sunday (example)
    In Cell B50 enter =COUNTIF(B3:B49,"P") <<< Will count the number of
    "P"s
    where you have data in cells B3:B49


    "Jason Morin" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote in message
    news:0ee101c50e8b$df049670$a601280a@phx.gbl...
    > This should get you started. To count the total # of P's
    > for John for January (assuming your data below is just
    > for January), try:
    >
    > =SUMPRODUCT((3:3="P")*(1:1="Sun"))
    >
    > where John is listed on row 3 and the Days are listed in
    > row 1.
    >
    > HTH
    > Jason
    > Atlanta, GA
    >
    > >-----Original Message-----
    > >I use Excel 2000
    > >I have sheet muster for my clients of January, 2005

    > like :
    > >( P = Present, A=Absent )
    > > A.....B.....C.....D.....
    > >Days Sun Mon Tue Wed
    > >Date 1 2 3 4
    > >
    > >1 John P A A P
    > >2 Lucy A P P A
    > >3
    > >Now I want to calculate the total Sundays, Mondays,

    > Tuesdays etc. present in
    > >that month. How can I do this?
    > >--
    > >Knowldege is Power
    > >.
    > >




  7. #7
    Ajit Munj
    Guest

    Re: Count for present & absent days & conversion of actual fig.

    It's great Jason. It was so simple! Now one more help.
    I have workbook named News.xls with 3 sheets namely Pricelist, Muster and
    Bill for the month of January, 2005.
    Muster contains presenty of each client, marked P for present & A for Absent
    A.....B.....C.....D.....
    Days Sun Mon Tue Wed
    Date 1 2 3 4
    1 John P A A P
    2 Lucy A P P A
    3

    Pricelist contains prices for news papers (amount in Indian Rupees)
    A.....B.....C.....D.......E.........F.......G.......H...
    Days Sun Mon Tue Wed Thu Fri Sat

    Times 5 4 4 4 4 4 3.50

    Economic 9 2 2 2 2 2 9

    Now I want to prepare bills in sheet "Bill". I want Excel to check first
    from Muster whether client is marked as "P" on 1st of Jan, 05 (Saturday), if
    so, he should take a price of Times newspaper for Saturday from sheet
    Pricelist i.e. Rs.5/-, if not, put 0.00, & so on.. Thus my sheet will show :


    A.....B.....C.....D.....E.......F......G.......H......I..Total
    Days Sat Sun Mon Tue Wed Thu Fri Sat
    Date 1 2 3 4

    1 John 4 5 4 4 4 0 0 4
    25.00

    Note : On Thu & Fri John was marked as "A"
    How can I perform this in sheet Bill.



    "Jason Morin" wrote:

    > You divide cells one by one with a formula in another
    > cell:
    >
    > =A1/100000
    >
    > or to do all of them at once, put 100000 in an open cell,
    > copy it, select your numbers in column A, and go to Edit
    > > Paste Special > Divide.

    >
    > HTH
    > Jason
    > Atlanta, Ga
    >
    > >-----Original Message-----
    > >Thanks Jason,It's working.
    > >I also want to convert actual numbers (a1=46556.4646,

    > a2=787979.79798 etc.)
    > >into lacs (i.e. a1=0.465564646, a2=7.87979.79798 etc.)

    > at one go and also one
    > >by one. How can I do this? I also want Excel to prompt

    > me for convesion in
    > >Lacs or Crores or Thousands? Pl. guide.
    > >
    > >Ajit
    > >"Jason Morin" wrote:
    > >
    > >> This should get you started. To count the total # of

    > P's
    > >> for John for January (assuming your data below is just
    > >> for January), try:
    > >>
    > >> =SUMPRODUCT((3:3="P")*(1:1="Sun"))
    > >>
    > >> where John is listed on row 3 and the Days are listed

    > in
    > >> row 1.
    > >>
    > >> HTH
    > >> Jason
    > >> Atlanta, GA
    > >>
    > >> >-----Original Message-----
    > >> >I use Excel 2000
    > >> >I have sheet muster for my clients of January, 2005
    > >> like :
    > >> >( P = Present, A=Absent )
    > >> > A.....B.....C.....D.....
    > >> >Days Sun Mon Tue Wed
    > >> >Date 1 2 3 4
    > >> >
    > >> >1 John P A A P
    > >> >2 Lucy A P P A
    > >> >3
    > >> >Now I want to calculate the total Sundays, Mondays,
    > >> Tuesdays etc. present in
    > >> >that month. How can I do this?
    > >> >--
    > >> >Knowldege is Power
    > >> >.
    > >> >
    > >>

    > >.
    > >

    >


  8. #8
    Ajit Munj
    Guest

    Re: Count for present & absent days

    Dear Jason,
    I tried your formula but some problem is there. I have made two sheets namely
    Muster & Bill. In Muster, instead of P & A, I have entered the name of
    newspaper. Hence I made some change in formula like
    :=SUMPRODUCT((Muster!3:3<>"")*(Muster!1:1="Sun")), it does not get tallied
    with the total number of days in that month. e.g. In month January, 2005
    there are 4 instances of Sun-Wed & 5 instances of Sat and Fri i.e. total 31
    days.(I have not kept any cell blank as I have put a newspaper from 1 to 31).
    After entering formula, it shows total 29 days, i.e. 5 instances of Sat. & 4
    instances of Sun-Fri. Where am I going wrong?. Secondly, by refering 4:4 and
    2:2, if accidently somebody enter a day and date, suppose in a far away cell
    IN2 and IN4, the formula will show additional day. Pl. guide.
    ajit

    "Jason Morin" wrote:

    > This should get you started. To count the total # of P's
    > for John for January (assuming your data below is just
    > for January), try:
    >
    > =SUMPRODUCT((3:3="P")*(1:1="Sun"))
    >
    > where John is listed on row 3 and the Days are listed in
    > row 1.
    >
    > HTH
    > Jason
    > Atlanta, GA
    >
    > >-----Original Message-----
    > >I use Excel 2000
    > >I have sheet muster for my clients of January, 2005

    > like :
    > >( P = Present, A=Absent )
    > > A.....B.....C.....D.....
    > >Days Sun Mon Tue Wed
    > >Date 1 2 3 4
    > >
    > >1 John P A A P
    > >2 Lucy A P P A
    > >3
    > >Now I want to calculate the total Sundays, Mondays,

    > Tuesdays etc. present in
    > >that month. How can I do this?
    > >--
    > >Knowldege is Power
    > >.
    > >

    >


  9. #9
    Registered User
    Join Date
    09-06-2013
    Location
    Winterville, NC, USA
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: Count for present & absent days

    Quote Originally Posted by Ajit Munj View Post
    Now I want to calculate the total Sundays, Mondays, Tuesdays etc. present in
    that month. How can I do this?
    --
    Knowldege is Power
    in row 1 first cell (A1) is blank (this is where you will your first Sunday's date will be placed. Cell B1 will be =A1+1 (that will give you Monday's date For cell C1 put =A1+2. D1 would read ]=A1+7 this will give you Sundays date. then the next cell (E1) would be ]=D1+1 which is Mondays date. then follow the pattern ( +7,+1 +2,+7 etc) do this about 5 times for a 31 day month.
    now if you don't want to see any date other than that month you can use this formula you cant you conditional formatting using formula =OR(DAY(A1)>DAY(B1),DAY(A1)>DAY(C1)) format text to white then use format painter to copy formatting across the cells.

    Hope this helps

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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