+ Reply to Thread
Results 1 to 3 of 3

Referencing separate workbook within SUMIFS / COUNTIFS

  1. 06-28-2019, 04:11 PM #1
    philfry
    philfry is offline
    Registered User
    Join Date
    01-30-2019
    Location
    Nashville, TN
    MS-Off Ver
    365
    Posts
    8

    Referencing separate workbook within SUMIFS / COUNTIFS

    I'm having a bit of trouble with regard to referencing data sources.

    I have several workbooks. One workbook contains summary information and data calculation worksheets. The other workbooks each contain data in specific sets (one workbook for certain conditions that all need to be together).

    I am using SUMIFS and COUNTIFS formulas to search through data (thousands of lines of high-thoroughput gathered data) and calculate some statistics based on certain identifiers within each data line. I am able to accomplish this without any issue if I can manually pointing to a specific worksheet. -- I want to edit these formulas to instead search an entire separate workbook for this identified data, and not just one worksheet within the workbook.

    Thoughts?



    Appreciate any help.
    Last edited by philfry; 06-28-2019 at 04:24 PM.
    Reply With QuoteRegister To Reply
  2. 06-28-2019, 09:24 PM #2
    Estevaoba
    Estevaoba is offline
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365 Version 2404
    Posts
    1,259

    Re: Referencing separate workbook within SUMIFS / COUNTIFS

    Assuming your data are in same columns throughout your workbook, make a list of worksheets’ names, then select those names and create a named range from Name Manager.
    Use that named range to run SUMIFS/COUNTIFS with SUMPRODUCT and INDIRECT functions.
    Something like this:

    =SUMPRODUCT(SUMIF(INDIRECT("'"&NamedRange&"'!A3:A100"),A3,INDIRECT("'"& NamedRange &"'!B3:B100")))

    Adjust ranges in formula accordingly.
    Reply With QuoteRegister To Reply
  3. 06-28-2019, 09:26 PM #3
    philfry
    philfry is offline
    Registered User
    Join Date
    01-30-2019
    Location
    Nashville, TN
    MS-Off Ver
    365
    Posts
    8

    Re: Referencing separate workbook within SUMIFS / COUNTIFS

    Data is all sorted in the same columns throughout. I’ll give this a try. Thanks!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Countifs or sumifs
    By jyadayada in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-26-2017, 07:59 AM
  2. [SOLVED] Countifs referencing data from another worksheet in the same workbook
    By benny_gc in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-26-2017, 06:14 PM
  3. Autofill Range Referencing Separate Workbook
    By joshuarobbins in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-28-2016, 03:01 PM
  4. Sumifs used referencing a closed workbook
    By kgallo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-20-2015, 06:57 PM
  5. COUNTIFS and SUMIFS and AVERAGEIFS workbook linking
    By PJR in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-07-2014, 01:24 PM
  6. Sumifs and countifs
    By JohnGault82 in forum Excel General
    Replies: 3
    Last Post: 02-03-2011, 05:31 PM
  7. Referencing separate workbook
    By coffcons in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 02-04-2008, 06:32 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1