+ Reply to Thread
Results 1 to 12 of 12

countif same range across multiple tabs

  1. #1
    Registered User
    Join Date
    08-23-2006
    Location
    Darlington, England
    MS-Off Ver
    Office 2013 Home & Business, Access 2016
    Posts
    23

    countif same range across multiple tabs

    Hi I have a countif formula as follows
    =COUNTIF('Jan 10'!B:B,A2)

    I have numerous tabs labelled with every month of the year "Jan 10" "Feb 10" etc.

    I want the formula to look up the same range on every tab but when I put this formula in it gives a "#value" error.
    =COUNTIF('Jan 10:Mar 10'!B:B,A2)

    Does anyone know of a way to do this thanks!

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

    Re: countif same range across multiple tabs

    Hello anthgav,

    Countif does not work in 3D. You need Countif.3D, a function available in the free morefunc.xll

    Download link: http://download.cnet.com/Morefunc/30...-10423159.html

  3. #3
    Registered User
    Join Date
    08-23-2006
    Location
    Darlington, England
    MS-Off Ver
    Office 2013 Home & Business, Access 2016
    Posts
    23

    Re: countif same range across multiple tabs

    thanks but unfortunately my works network does not allow the download I will have to download at home and bring in on a stick then I will try it.

  4. #4
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: countif same range across multiple tabs

    Format cell A3:A5 as text
    In A3: Jan 10
    A4: Feb 10
    A5: Mar 10
    Define name range call "MySheets" no quote in A3:A5

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!B:B"),A2))

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

    Re: countif same range across multiple tabs

    Nice one, Tethless mama!

    Interesting to watch this one in the Evaluate Formula tool.

  6. #6
    Registered User
    Join Date
    03-24-2009
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: countif same range across multiple tabs

    Good one theethless mama!

  7. #7
    Forum Moderator DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Suffolk, UK
    MS-Off Ver
    2002, 2007 & 2010
    Posts
    21,520

    Re: countif same range across multiple tabs

    John McGimpsey has a quick overview of the options re: conditional 3D calcs:

    http://www.mcgimpsey.com/excel/threedsumif.html

    I'm sure teethless mama would agree that though elegant the SUMPRODUCT is Volatile, Volatile Arrays & Sumproducts are generally best avoided esp. if used en masse.

    Generally speaking the use of intermediate tables is the more efficient approach - though no where near as elegant unfortunately.

  8. #8
    Registered User
    Join Date
    08-23-2006
    Location
    Darlington, England
    MS-Off Ver
    Office 2013 Home & Business, Access 2016
    Posts
    23

    Re: countif same range across multiple tabs

    Thanks for your input every one its greatly appreciated.

    the formula that teethless mama gave me is perfect for what I need. Just one more problem to help complete my sheet. I have a cell with this formula

    =SUMIF('Jan 10'!B:B,A2,'Jan 10'!E:E)

    It sums all the quantities adjacent to the cells that have been counted in the countif formula. But again I want it to sumif across the same multiple sheets as the countif.
    Can you help?

  9. #9
    Registered User
    Join Date
    08-23-2006
    Location
    Darlington, England
    MS-Off Ver
    Office 2013 Home & Business, Access 2016
    Posts
    23

    Re: countif same range across multiple tabs

    I understand what your saying DonkeyOte about the sheet being volatile, every time I adjust any data on any tabs the sheet takes ages to re-calculate cells. Perhaps the best solution for me here is to create a summary sheet with every months formulas on its own row and then just sum them all up at the end.
    I was trying to be clever by creating a formula that would do it all in one cell without all the extra work but it looks like I will have to.
    In the example I only used 3 months but in reality my information goes back well over a year and also the formulas goes down 1500 lines for 1500 different countifs hence why teethless mamas formula became volatile.

    I might have a look at "morefunc.xll" and see what thats all about but the sheet is used on multiple computers on a network will that make any difference using "morefunc.xll"

    Still open to any suggestions though!
    Last edited by anthgav; 04-13-2010 at 06:17 PM.

  10. #10
    Registered User
    Join Date
    06-01-2010
    Location
    Arlington, VA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: countif same range across multiple tabs

    Quote Originally Posted by teylyn View Post
    Hello anthgav,

    Countif does not work in 3D. You need Countif.3D, a function available in the free morefunc.xll

    Download link: http://download.cnet.com/Morefunc/30...-10423159.html
    Hello,

    I am also trying to use COUNTIF() across multiple sheets and was hoping to utilize COUNTIF.3D. Thank you for providing this link, but I downloaded morefunc from here, and I still can't seem to use COUNTIF.3D. I can see the Morefunc menu in my add formula window, but COUNTIF.3D does not appear there. From what I've been able to find out about this function, it appears in Morefunc 5.05; the link above appears to be Morefunc 5 (perhaps 5.0?).

    Am I missing something? If so I would really appreciate any help anyone can provide. I tried downloading directly from what I thought was the author's website, but the download link seems to be broken there. http://xcell05.free.fr/morefunc/english/#Download If anyone has an alternate link it would be much appreciated.

  11. #11
    Registered User
    Join Date
    01-24-2016
    Location
    Morganton, nc
    MS-Off Ver
    2003
    Posts
    5

    Re: countif "0" across multiple tabs

    I'm trying to count cells across multiple tabs (113) that contain the number 0. I've tried =COUNTIF('Lastname, firstname1':'Lastname, fristname 113'!E6,"*" & "0" & "*") and get error message. Any help??

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    47,995

    Re: countif same range across multiple tabs

    DCACTT welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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