+ Reply to Thread
Results 1 to 3 of 3

Countifs across multiple tabs

  1. #1
    Registered User
    Join Date
    09-15-2010
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2007
    Posts
    25

    Countifs across multiple tabs

    I am trying to count how many time the name "Bryan" appears in Range f:f across mutiple sheets in my worksheet.

    My worksheet contains 9 sheets I am setting up my formula in sheet "Riders" column 2 where I have column of names in column 1. Sheets "28-310 - 36-310" have work assignments with the corresponding names in column F. Sheet "Riders" is being used to tally the number of times the names appear in the following sheets.

    The formula I have tried unsuccesfully is =COUNTIFS('28-310'!F:F,Riders!A6,'29-310'!F:F,Riders!A6...). I've tried a variation of a suggestion from a previous post about the averageif function but was unsuccesful there too.

    Thanks for any assistance you can render.
    Last edited by mknispel; 08-20-2012 at 03:43 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Countifs across multiple tabs

    You can either pick a cell in each sheet and do a COUNTIF in each sheet, then sum those in one sheet with formula like: =SUM('28-310:36-310'!X1) where X1 was chosen cell in each sheet.

    or in the summary sheet, list the sheetnames in 9 vertical says, say X1:X9, then try:

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&$X$1:$X$9&"'!F:F"),Riders!A6))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    08-10-2012
    Location
    Rotterdam, Holland
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: Countifs across multiple tabs

    The countifs-function does not allow to count in multiple arrays/ranges. I thought this function would do the trick, =countif('28-310:36-310'!F:F,Riders!A6) , however it doesn't work that way unfortunately. (This only works if the sheets you want to "look" into are sorted next to each other and 28-310 is the first and 36-310 is last) Can anyone help me where I go wrong with my suggestion, or does this just not work with countif-function?

    However because it does not work i would suggest using =countif('28-310'!F:F,Riders!A6)+...+countif('36-310'!F:F,Riders!A6) . So you will end up with 9 countif-functions within 1 cell. It probably can be done more easily, but I can't come up with the function at this moment.

+ 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