Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need to total fields within a roster. The shifts will vary and they will either have an "a", "A", "b" or "B". I need to total these amounts against the equivalent shift i.e. 8 (for an 0800 start), 1300 (for a 1300 start) etc.

    I used to be able to use the count feature as I just used the numeric but now we are making up teams "A" and "a" and "B" and "b".

    I have attached a copy of one roster page. Looked at the SUM array but it will only return criteria that matches all. DCOUNTA but not sure if this is what I am after. Can someone point me in the right direction?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In cell B60, you can use =COUNTIF(B$4:B$56,$A60&"*")
    Fill down, then right or right, then down.

  3. #3
    New Lounger
    Join Date
    Nov 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks HansV - something so simple. Was thinking too hard when an easy option was there. Very much appreciated. Jac

  4. #4
    New Lounger
    Join Date
    Nov 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans

    Need to now add the total shifts by a particular doctor by the shift i.e. 7, 7a, 7A, 8, 1300 etc. I assume I need to use the 3D function as I have fortnightly rosters. I now have some shifts that are 7 or 7a. Do I use the Sum array on grouped sheets? I need to find out how many of each shift type each doctor has done to give me total. I can't use the Countif in an array.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='jacquir' post='764283' date='09-Mar-2009 06:54']Hi Hans

    Need to now add the total shifts by a particular doctor by the shift i.e. 7, 7a, 7A, 8, 1300 etc. I assume I need to use the 3D function as I have fortnightly rosters. I now have some shifts that are 7 or 7a. Do I use the Sum array on grouped sheets? I need to find out how many of each shift type each doctor has done to give me total. I can't use the Countif in an array.[/quote]
    See here for 3D functions that perform a Countif or Sumif over several sheets.

  6. #6
    New Lounger
    Join Date
    Nov 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for link but way beyond me...Jacqui

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='jacquir' post='764530' date='10-Mar-2009 06:38']Thanks for link but way beyond me...Jacqui[/quote]
    You don't have to understand the code, you can simply copy it into a module, and use it in formulas.

Posting Permissions

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