0

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,

madQuestions
  • 109
  • 1
  • 7
  • Can you explain a bit more about your `#SPILL!` errors? `SUMPRODUCT` works fine on closed workbooks. – Jos Woolley Mar 13 '23 at 19:59
  • @JosWoolley can you please show an example using the data I provided how to use column A as non null criteria to count or sum other columns? – madQuestions Mar 13 '23 at 20:07
  • I wouldn't use the entire column, for a start, which is a terribly resource-heavy operation for a function such as `SUMPRODUCT`. I'd first determine the last-used cell in that column and set the range accordingly. To do that efficiently, I'd need to know whether the content of a given column was text, numeric, or a mixture of both, and also whether it contained any null strings, e.g. as a result of formulas in that column set to return `""` under certain conditions. – Jos Woolley Mar 13 '23 at 20:11
  • @JosWoolley Sure. In this example, all columns can be a mix of text and numbers as the data I'm working with has notes, NAs, Values...etc in columns that should be numeric. – madQuestions Mar 13 '23 at 20:17
  • What about null strings, as I also enquired about? – Jos Woolley Mar 13 '23 at 20:18
  • @JosWoolley the column that has Id's that I would use as the last row finder will not have nulls. – madQuestions Mar 13 '23 at 20:19
  • Thanks. And just to confirm, the ID column can also contain text, as well as numerics? – Jos Woolley Mar 13 '23 at 20:20
  • @JosWoolley Correct. – madQuestions Mar 13 '23 at 20:26

2 Answers2

1

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.

Jos Woolley
  • 8,564
  • 2
  • 4
  • 9
  • Wow! That gets the last row count. That will come in handy. What is this wizardry?? – madQuestions Mar 13 '23 at 20:49
  • It using binary searching, and so will be potentially thousands of times faster than a linear search. You often see examples of linear searches for finding the last-used cell in a column, e.g. `=MAX(IF([SourceData.xlsx]Sheet1!$A:$A<>"",ROW([SourceData.xlsx]Sheet1!$A:$A)))` but by nature they're incredibly inefficient as they have to process all one million plus cells in the column. – Jos Woolley Mar 14 '23 at 05:37
0

I would use PowerQuery group by for questions 1,2, & 3. The Powerquery is designed to be capable of refreshing calculations based on data sources that have new/changed data. With in PowerQuery you can filter and add logic for nulls. Based on what you are talking about such as cleaning up data it is the best beast.

Shane S
  • 1,747
  • 14
  • 31