Search code examples
excelcountifsumifs

Excel Workaround Links To Closed Workbooks

I'm trying to use workaround formulas for CountIf and SumIf as they don't work when used in linked spreadsheets if the source workbook is closed. I've tried using SumProduct as well as Count(If) but received #Spill! Errors.

If you had the following data in a SourceData.xlsx:

|LibraryID  |FeesDue  |Returned 
|1          | 0       | Y       
|2          |#N/A     | Y      
|3          |100      |

I then need totala in the other spreadheet MergedData.xlsx

|SumOfFees |TotalCountOfFees  |TotalCountOfReturned
| 100      | 1                |2
  • Q1.What formula would you use to sum fees that ignores text/errors?
  • Q2 A.What formula would you use to get a count of records in column B greater than 0? B.Whay formula would I use to find a count of all records that equal zero? (I get over a million because I have to use the whole column as the row count will vary)
  • Q3.What formula would you use to get a count of Ys in the returned column

**I have to use the whole column as a range as the source data will change so I can't specify a range. However, I'm trying to include using column A where not null to help create a dynamic range which is what I think is causing the spill/error messages. Also notice when trying to sum fees there is text in the column. How do you get around that not using a sumif. Also,

Solution

  • Use

    =MAX(MATCH(IF({0;1},"Ω",77^77),[SourceData.xlsx]Sheet1!$A:$A))

    to determine the last-used cell in column A of the SourceData workbook and incorporate this within your SUMPRODUCT formulas.