+ Reply to Thread
Results 1 to 10 of 10

COUNTIF across multiple sheets

  1. #1
    Registered User
    Join Date
    10-25-2013
    Location
    Croatia
    MS-Off Ver
    Excel 2010
    Posts
    17

    COUNTIF across multiple sheets

    How do I use countif formula across sheets?
    I provided simplified file with short example so if anyone can help it will be appreciated.
    Cross COUNT.xlsx
    I need to count how many times number 5 appears in, for example, A2 cells, and how many times number 4 appears in, for example, A3 cells across multiple sheets. Also if, for example, there is no data at all in all corresponding cells (for example, Sheet1A2,Sheet2A2,Sheet3A2,Sheet4A2 and Sheet5A2) formula should return zero in Sheet6A2.
    Last edited by lvl-48; 10-29-2013 at 03:48 PM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: COUNTIF across multiple sheets

    Perhaps have a look at this 3D function topic

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: COUNTIF across multiple sheets

    Try these...

    =SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&{1,2,3,4,5}&"'!A2"),5))

    =SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&{1,2,3,4,5}&"'!A3"),4))

    You might have to replace the commas with semi-colons.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: COUNTIF across multiple sheets

    It's still doable, you just have to use a workaround.

    First, break out the number IF number you're countinging.

    Next, you need to list the sheets and then give that range a name.

    I called mine threedeecountif

    Then, you can use a formula like:

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&threedeecountif&"'!A2:A10"),B2)) where B2 = the IF we're looking for

    And it will count all of the sheets listed in that range.

    3D Countif.xlsx

  5. #5
    Registered User
    Join Date
    10-25-2013
    Location
    Croatia
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: COUNTIF across multiple sheets

    That worked perfect with semi-colons. Thanks for fast help!

    Two more things.
    1.How do I adopt formula if I change the names of sheets? Just replace 1,2,3,4,5 with corresponding sheet names doesn't work

    2.If I want to ad new sheets I should make first and last as blank/hiden sheets?

  6. #6
    Forum Contributor vamosj's Avatar
    Join Date
    04-23-2004
    Location
    Oregon
    MS-Off Ver
    2010
    Posts
    294

    Re: COUNTIF across multiple sheets

    Another solution


    =SUM(COUNTIF(Sheet1!A2,5),COUNTIF(Sheet2!A2,5),COUNTIF(Sheet3!A2,5),COUNTIF(Sheet4!A2,5),COUNTIF(Sheet5!A2,5))
    Janos S. Vamos
    Data Systems Technician/Fire Controlman PO1(SW/AW)
    US Navy Retired


    Remember, Record Macro can be your friend for figuring out solutions.

    Good or Bad, let me know how I did by clicking on the "Add Reputation" * just to the lower left of here. it only takes a few seconds to let someone know.

  7. #7
    Registered User
    Join Date
    10-25-2013
    Location
    Croatia
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: COUNTIF across multiple sheets

    Quote Originally Posted by vamosj View Post
    Another solution


    =SUM(COUNTIF(Sheet1!A2,5),COUNTIF(Sheet2!A2,5),COUNTIF(Sheet3!A2,5),COUNTIF(Sheet4!A2,5),COUNTIF(Sheet5!A2,5))
    I like those simple solutions Thanks it worked perfect with semi-colons!
    When I need to add new sheet I'll just adopt formula like: instead COUNTIF(Sheet19!A2,5) COUNTIF(name of the sheet!A2,5)
    Last edited by lvl-48; 10-29-2013 at 03:58 PM.

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: COUNTIF across multiple sheets

    If you have different random sheet names then use daffodil11's suggestion and create a list of the sheet names.

    If you're going to add new sheets then the best option is to use daffodil11's suggestion but make the sheet list a dynamic range.

  9. #9
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: COUNTIF across multiple sheets

    Lord Valko likes my idea! I'll remember this forever.

    Just be sure to convert the list of Sheet names to a table so that the named reference automatically adjusts.

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: COUNTIF across multiple sheets

    Well, you knew the best answer had to come from PA!

    I'll also throw some rep your way!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Countif, Multiple Criteria, Multiple Sheets, Excel 2003
    By shyammankoo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-07-2013, 06:50 AM
  2. COUNTIF multiple sheets
    By Schalk in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-19-2011, 06:33 AM
  3. CountIf across multiple sheets
    By Zhenya in forum Excel General
    Replies: 2
    Last Post: 09-16-2011, 12:56 PM
  4. Using countif to look at multiple sheets???
    By lukasj13 in forum Excel General
    Replies: 4
    Last Post: 10-08-2008, 01:57 PM
  5. COUNTIF across multiple sheets
    By Gizmo63 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-10-2006, 06:45 AM

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