Page 1 of 4 123 ... LastLast
Results 1 to 10 of 36

COUNTIF across multiple worksheets

This is a discussion on COUNTIF across multiple worksheets within the Excel Questions forums, part of the Question Forums category; Would someone know what I am doing wrong here? I need to count the frequency/occurance of letters "A", "B", "C" ...

  1. #1
    New Member
    Join Date
    Jan 2003
    Posts
    1

    Default

    Would someone know what I am doing wrong here? I need to count the frequency/occurance of letters "A", "B", "C" etc. that occur in the exact same cell in multiple worksheets of a single workbook. COUNTIF works ok on a single sheet query for me but gives the "VALUE" error message when I insert the sheet1:sheetxxx range.
    Thank you,
    Stephen Butter

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,201

    Default

    COUNTIF is not applicable as a 3D function.

    Insert 2 new worksheets and name them First and Last. Place the relevant worksheets between First and Last.

    Now use:

    =COUNTIF.3D(First:Last!Range,Condition)

    This function is part of the morefunc.xll add-in which you need to download & install.

    Addendum. Otherwise, follow Ogilvy's advise.


    _________________
  3. Download morefunc

  4. For more on morefunc, see Function Descriptions


  5. [ This Message was edited by: Aladin Akyurek on 2003-01-26 17:12 ]

  6. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,140

    Default

    Hi

    As far as I know Countif will not allow multi sheets selections, so no this can not be done as you request.

    Im not sure how to get around this, but im very confident that this is not a built in function to excel as Countif is. So I assume VBA will be needed or UDF at the very least

    Kind rdgs

    Jack

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  7. #4
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    4,965

    Default

    Hi Stephen,

    Like Jack said,It is probably necessary to use a UDF.

    I have created a UDF called CntIf3D which stands for 3 Dimensional CountIf.

    The code is as follows:


    Public Function CntIf3D(rng As Range, V As Variant, ParamArray arglist() As Variant)
    Application.Volatile

    CntIf3D = 0
    For Each arg In arglist
    CntIf3D = WorksheetFunction.CountIf(Sheets(arg).Range(rng.Address), V) + CntIf3D
    Next

    End Function


    This UDFunction takes 2 Arguments like the Excel Built-in CountIf Function ( The Range and the value to be counted ) + an indefinite number of Optional arguments which represent each the name of each worksheet included in the count for their respective range.

    Below is a sample worksheet using the Function in cells D1 Trough D3.
    Note that the formulas refer to all the 4 sheets in the workbook namely : Sheets1,2,3 and "Sales".For eg Cell D1 returns 4 which is the total number of cells containing the letter "A" in Column(A)of all the worksheets in the workbook :

    ******** ******************** ************************************************************************>
    Microsoft Excel - 178.xls___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    1
    JAAFAR**4*
    2
    H**3*
    3
    100**3*
    4
    F1****
    5
    10****
    6
    0.5****
    7
    A****
    8
    COBOS****
    9
    JAAFAR****
    10
    F****
    sales*

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.



    Note that The order of the worksheets inside the function is not important which makes the use of the Function very convinient.


    Hope this helps.

    Jaafar.








    [ This Message was edited by: rafaaj2000 on 2003-01-26 20:30 ]

    [ This Message was edited by: rafaaj2000 on 2003-01-26 20:31 ]

    [ This Message was edited by: rafaaj2000 on 2003-01-26 20:33 ]

  8. #5
    New Member
    Join Date
    May 2010
    Posts
    47

    Default Re: COUNTIF across multiple worksheets

    Jaffar Tribak, great formula for going accross multiple sheets!
    However I have a question:

    Your Code helps me going through multiple sheets but I cant enter mutplite criterias and ranges as with the formula COUNTIFS

    For example, with CONTIFS,you can add muptiple criterias and ranges like so:
    =+COUNTIFS(A:A;"A";H:H;"B";AC:AC;"C")

    You see ?
    I need to include the Range A:A , H:H and AC:AC through mutplie sheets to find the value "A" , "B" and "C"

    The formula you did allows to do though multiple sheets; but how would you go add to this mutpliple criteria and range please ?

    thanks a lot for your help

  9. #6
    New Member
    Join Date
    May 2010
    Posts
    47

    Default Re: COUNTIF across multiple worksheets

    Your formula gives this =CntIf3D(A:A;"A";"Sheet1";"Sheet2";"Sheet3")
    How would you add range H:H with "B" as criteria and range AC:AC with "C"

  10. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,201

    Default Re: COUNTIF across multiple worksheets

    Quote Originally Posted by Jeffy14 View Post
    Jaffar Tribak, great formula for going accross multiple sheets!
    However I have a question:

    Your Code helps me going through multiple sheets but I cant enter mutplite criterias and ranges as with the formula COUNTIFS

    For example, with CONTIFS,you can add muptiple criterias and ranges like so:
    =+COUNTIFS(A:A;"A";H:H;"B";AC:AC;"C")

    You see ?
    I need to include the Range A:A , H:H and AC:AC through mutplie sheets to find the value "A" , "B" and "C"

    The formula you did allows to do though multiple sheets; but how would you go add to this mutpliple criteria and range please ?

    thanks a lot for your help
    Create a range housing the relevant sheet names, name this range SheetList, and invoke:

    Code:
    =SUMPRODUCT(COUNTIFS(
       INDIRECT("'"&SheetList&"'!A:A"),"A",
       INDIRECT("'"&SheetList&"'!H:H"),"B",
       INDIRECT("'"&SheetList&"'!AC:AC"),"C"))
    Assuming too much and qualifying too much are two faces of the same problem.

  11. #8
    New Member
    Join Date
    May 2010
    Posts
    47

    Default Re: COUNTIF across multiple worksheets

    thx a lot for your answer Aladin Akyurek.
    However, I'm gonna ask a really stupid quesiton but how to create a range having multiple sheets ???
    lets say I have only Sheet1 and Sheet2

    SheetList= ??

    thanks a lot

  12. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,201

    Default Re: COUNTIF across multiple worksheets

    Quote Originally Posted by Jeffy14 View Post
    thx a lot for your answer Aladin Akyurek.
    However, I'm gonna ask a really stupid quesiton but how to create a range having multiple sheets ???
    lets say I have only Sheet1 and Sheet2

    SheetList= ??

    thanks a lot
    Insert a sheet, name it Admin. Go to Admin, insert Sheet1 in A2 and Sheet2 in A3. Select A2:A3, go the Name Box on the Formula Bar, type SheetList, and hit the enter key.
    Assuming too much and qualifying too much are two faces of the same problem.

  13. #10
    New Member
    Join Date
    May 2010
    Posts
    47

    Default Re: COUNTIF across multiple worksheets

    Hi Aladin,
    Thanks for your answer!
    However, you will probably think I'm a retard but I dont understand what you want me to do:

    http://img94.imageshack.us/i/excelf.jpg/
    thats what you want me to do ?
    then I I go in the formula bar to put SheetList it changes the Cell name in A2, that is Sheet1

    I'm not sure that's what you meant

Page 1 of 4 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com