+ Reply to Thread
Results 1 to 6 of 6

Sumifs/Countifs not working across linked workbooks

  1. #1
    Forum Contributor
    Join Date
    11-07-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    126

    Sumifs/Countifs not working across linked workbooks

    Hi all,

    I am having an issue with both sumifs and countifs not working across linked workbooks if they are closed. I know that there is an issue in this aspect and have managed to solve my countif and sumif formulas with sum(if( / count(if( or sumproduct(1*( workarounds but, I am not sure how to do it for multiple criteria (or if it can even be done).

    Any help would be great.
    Last edited by elleb; 03-13-2018 at 05:36 AM.

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Sumifs/Countifs not working across linked workbooks

    Hi,

    Either of the alternatives should work with multiple criteria. What are the formulas that you are trying to alter?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,991

    Re: Sumifs/Countifs not working across linked workbooks

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  4. #4
    Forum Contributor
    Join Date
    11-07-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    126

    Re: Sumifs/Countifs not working across linked workbooks

    Hi,

    thanks for the reply, sorry its slightly confusing for me as I am used to wording the formula differently depending on if there is an ifs at the end!

    So the formulas I have are: -

    countif("linked spreadsheet range",cell reference)-countifs("linked spreadsheet range",condition1,"linked spreadsheet range","*part text search*"

    sumif("linked spreadsheet range",cell reference,"linked spreadsheet range")-sumifs("linked spreadsheet range","linked spreadsheet range",cell reference,"linked spreadsheet range","*part text search*"

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Sumifs/Countifs not working across linked workbooks

    I think the first one would be
    =SUMPRODUCT(--("linked spreadsheet range"=cell reference))-SUMPRODUCT(("linked spreadsheet range"=condition1)*(ISNUMBER(SEARCH("part text search","linked spreadsheet range"))))
    and the second would be
    =SUMPRODUCT(--("linked spreadsheet range"=cell reference),"linked spreadsheet number range")-SUMPRODUCT(("linked spreadsheet range"=condition1)*(ISNUMBER(SEARCH("part text search","linked spreadsheet range"))),"linked spreadsheet number range")

  6. #6
    Forum Contributor
    Join Date
    11-07-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    126

    Re: Sumifs/Countifs not working across linked workbooks

    works perfectly, thanks for the help!

+ 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. [SOLVED] Array formula not working with Linked Workbooks
    By Stuepef in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-29-2017, 12:12 PM
  2. SUmifs / countifs?
    By IsabellaLearning in forum Excel General
    Replies: 2
    Last Post: 11-09-2017, 05:52 PM
  3. Replies: 0
    Last Post: 08-22-2017, 03:49 PM
  4. Countifs and Sumifs not working...y?
    By biznez in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-18-2016, 06:10 AM
  5. Linked workbooks working on every machine
    By albemazz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-01-2014, 09:03 AM
  6. Countifs or Sumifs
    By RalstonT14612 in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 11-30-2014, 02:02 PM
  7. Sumifs and countifs
    By JohnGault82 in forum Excel General
    Replies: 3
    Last Post: 02-03-2011, 05:31 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1