+ Reply to Thread
Results 1 to 5 of 5

Counting the digit 2 accross mulitple sheets

  1. #1
    Registered User
    Join Date
    01-15-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Thumbs up Counting the digit 2 accross mulitple sheets

    I'm new to excel, I'm using the excel for dummies book. I'm working on a employee absence log.

    I have 13 sheets, January thru December and a Total sheet. The 12 month sheets list the employee names in the columns a2 thru a21, then the dates are in rows b thru af. I want to use 1=absent, 2=late, 3=approved day off. I need a running total in the "Total "sheet. I think I need to use countif like this:

    =countif[May:June:July:August:September:October:November:December!2b,2af"1")
    =countif[May:June:July:August:September:October:November:December!2b,2af"2")
    =countif[May:June:July:August:September:October:November:December!2b,2af"3")

    It's not working and I must be missing something basic here. Can anyone point me in the right direction? I have attached the book. Thank you in advance for your time.
    Attached Files Attached Files
    Last edited by deadscott; 05-13-2010 at 09:38 PM.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Counting the digit 2 accross mulitple sheets

    several things:

    countif() does not work across sheets, only in the current sheet.

    a 3D reference would look like May:December!A1 and not listing all sheets in between

    you start with a [ bracket instead of (

    what is 2b? Do you mean B2 instead?

    the parameter after the last comma is not valid countif syntax.

    In short: you cannot do what you want to do with countif. Do countif() on each sheet, in the same cell, and then do a 3D sum

    =Sum(May:December!A1)

    cheers

  3. #3
    Registered User
    Join Date
    01-15-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Counting the digit 2 accross mulitple sheets

    Thanks for your help. I get this part =SUM(May:December!B2:AF2) I don't need it to sum the numbers, I need it to tell me how many times the number 2 shows up in this range on all the month sheets in the total sheet in cell b2. I've uploaded a new book. In the May sheet in cells B2:AF2 the number 2 is entered three times. So in the total sheet in cell B2 I need it to read 3
    Attached Files Attached Files

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Counting the digit 2 accross mulitple sheets

    Do a countif per person in each sheet. Then do a 3d sum on the total sheet.

    see attached

    Since you've just registered with this forum, can you please put in your correct Excel version? You specified Excel 2003, but you're clearly using a later version. There are significant differences between Excel 2003 and later versions, so if you want correct answers first time, knowing your version is a must.

    cheers
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-15-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Counting the digit 2 accross mulitple sheets

    Thank you for your help. I've got a lot to learn.

+ 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