+ Reply to Thread
Results 1 to 7 of 7

COUNTIF across different sheets

  1. #1
    Registered User
    Join Date
    12-17-2019
    Location
    Leeds, UK
    MS-Off Ver
    365
    Posts
    59

    Exclamation COUNTIF across different sheets

    Hi all,
    I have a spreadsheet with 17 sheets, each named data 2016, data 2015... and so on til data 2000. Each sheet has car listings and spec, with car manufacturer in the C column.
    I'm trying to compute a COUNTIF formula that counts all cells named 'Volkswagen' in the C column of all 17 sheets combined, but I'm really struggling to get the correct punctuation needed for it to work.
    What I have at the moment is as follows: (i've only done 2016 to 2013 for simplicity, the principle is of course the same)
    =COUNTIF(INDIRECT("'{2016 ,2015 ,2014 ,2013 }&" data'!C:C"),"VOLKSWAGEN")

    I know I could write out a long formula of 17 Countif functions, each for each year, but I'd like to get it condensed to the formula above. Any help would be appreciated. Thanks!
    Attached Files Attached Files
    Last edited by aurelien_21; 12-18-2019 at 08:05 AM.

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: COUNTIF across different sheets

    Try this

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&ROW(2000:2016)&"'!C:C"),"Volkswagen"))
    Last edited by Bob Phillips; 12-17-2019 at 09:03 PM.

  3. #3
    Registered User
    Join Date
    12-17-2019
    Location
    Leeds, UK
    MS-Off Ver
    365
    Posts
    59

    Re: COUNTIF across different sheets

    Hi Bob,
    No that also gives a #REF! outcome. I've attached a sample of the spreadsheet in the original thread if that helps.

    Cheers

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: COUNTIF across different sheets

    Change the sheet names to simply 2015 and 2016, than try this:

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&{"2015";"2016"}&"'!B:B"),"Volkswagen"))
    Last edited by AliGW; 12-18-2019 at 08:33 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: COUNTIF across different sheets

    Try this on the full data

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&ROW(2000:2016)&" data'!B:B"),"Volkswagen"))

  6. #6
    Registered User
    Join Date
    12-17-2019
    Location
    Leeds, UK
    MS-Off Ver
    365
    Posts
    59

    Re: COUNTIF across different sheets

    Hi Ali, thanks for your reply. Yes that does work, however I need to keep the '*year* data' format to ensure the model is dynamic when new years of data are added.
    Thanks again.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: COUNTIF across different sheets

    Try Bob's suggestion, then.

+ 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. Sum & Countif from Multiple workbooks
    By SeanBigger in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-27-2019, 09:32 AM
  2. Countif between 2 open workbooks without liking them both
    By mustafasb in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-01-2017, 09:56 AM
  3. [SOLVED] countif across workbooks
    By bimo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-25-2014, 09:54 AM
  4. [SOLVED] Using IF and COUNTIF to show changes between 2 workbooks
    By rssfed23 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-13-2013, 06:50 AM
  5. Using countif function across workbooks
    By StokeRich in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-28-2013, 09:08 AM
  6. [SOLVED] Countif across workbooks
    By vivekmartin in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-13-2013, 01:24 PM
  7. Countif between two workbooks!
    By RadioX in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-22-2012, 01:42 PM

Tags for this Thread

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