+ Reply to Thread
Results 1 to 6 of 6

Count if function over more worksheets?

  1. #1
    Registered User
    Join Date
    12-11-2009
    Location
    Luang Prabang, Laos
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    33

    Count if function over more worksheets?

    Hi,

    Is it possible to count a certain occurrence over more worksheets?

    I tried =COUNTIF(Jan-Dec!C:C,"EXO") And trying to count all occurrence of "EXO" in C:C in the 12 different worksheets. Unfortunately this does not work.

    Anybody another suggestion?

    Thanks in advance,

    ADB

  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: Count if function over more worksheets?

    Countif does not work in 3D. You will need Countif3D, which is one of the functions available if you install the free morefunc.xll, available from http://download.cnet.com/Morefunc/30...-10423159.html

  3. #3
    Registered User
    Join Date
    07-07-2009
    Location
    Karachi
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Count if function over more worksheets?

    Hi.

    If you list your worksheet names in column some where and assigning a named range "Mylist"

    then try this formula

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&Mylist&"'!c1:c300"),"exo"))

    for speeding purpose instead of using whole column reference use actual last column with sumproduct.
    adjust your ranges & referencing accordingly.



    regards

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Count if function over more worksheets?

    Quote Originally Posted by sheryar
    for speeding purpose instead of using whole column reference use actual last column with sumproduct.
    Very true for those using XL2007 - prior to that version I'm afraid the use of entire column references with SUMPRODUCT/Array would result in #NUM! error.

    The downside to the SUMPRODUCT approach is that it is not particularly efficient, in conjunction with INDIRECT it becomes Volatile.
    If you're using this formulae to populate a matrix (ie using a significant number of these formulae) the performance of your file will be affected (adversely).

    If you are indeed populating a matrix with these formulae (ie different criteria - EXO etc) then IMO (FWIW) you would be best served conducting 12 separate COUNTIFs (1 per sheet) and simply summing the results.
    Though less elegant this approach is more efficient and non-volatile. Unfortunately in XL elegance often comes at a cost (performance).

  5. #5
    Registered User
    Join Date
    07-07-2009
    Location
    Karachi
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Count if function over more worksheets?

    Hi Dk.

    Suppose on each sheet a1 has the countif function =counti(c1:c10,"exo")
    and on last sheet just use

    =(sheet1:shee12!a1)

    will this be elegant than using indirect with sumproduct.

    regards

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Count if function over more worksheets?

    sheryar, that would be a similar approach to that outlined already

    Quote Originally Posted by D.O
    ...conducting 12 separate COUNTIFs (1 per sheet) and simply summing the results
    Whether the separate COUNTIFs are stored in a single summary table (as above) or are stored on the monthly sheet is down to preference IMO.

    The single sheet avoids the need even for 3D SUM, however, the formulae for the individual monthly sheets could be applied in unison (grouped sheets) and might be quicker to configure.

    For the OP - all of the techniques described thus far are covered (albeit in SUMIF form) on John McGimpsey's (MS Excel MVP) page: http://www.mcgimpsey.com/excel/threedsumif.html
    ie - don't just take our word for it

+ 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