Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Counting total numbers (Excel 2003)

    Hi!
    I have some problems writing a formula where I could count how many numbers of a kind i have on one particular column through out 10 different worksheets.

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Counting total numbers (Excel 2003)

    Could you attach a sample?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Counting total numbers (Excel 2003)

    I tried using the "countif" function but it only works for ranges and criteria on one sheet (Ex- =COUNTIF(B3:B1000,2) ). When I try multiple sheets it returns #VALUE! error. Is there a similar function that works for multiple sheets?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting total numbers (Excel 2003)

    However tedious it may be, you'll have to use

    =COUNTIF('H 26-05'!B3:B1000,2)+COUNTIF('E 27-05'!B3:B1000,2)+COUNTIF('F 28-05'!B3:B1000,2)

  5. #5
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting total numbers (Excel 2003)

    Lastcall:

    As an alternative, if each of your sheets has the COUNTIF in the same cell (I5 in sheet 1) then you can sum 'through' the sheets on sheet 1 with:

    =SUM('H 26-05:F 28-05'!I5)

    The number of sheets doesn't matter, as long as you reference the first and last sheets.

    (Note the single quote (') marks enclosing the sheet range, because of non-alphanumeric characters in the sheet names.)

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  6. #6
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Counting total numbers (Excel 2003)

    thanks for the help, it made it easier for me.

  7. #7
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Counting total numbers (Excel 2003)

    Thanks for the Help, that also got me the results that i was looking

  8. #8
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting total numbers (Excel 2003)

    List your sheet names ina range and name that range. In this example, I've named it "sheets" without the quotes. Then use,

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!B3:B100 "),2))

    Another option with the Morefunc addin is,

    =COUNTIF.3D(Sheet1:Sheet17!$B$3:$B$100,2)

Posting Permissions

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