Forum Discussion
Issue with FILTER function not displaying all results
So I am using the filter function to summarize information from a different sheet. I have entered the following =FILTER(Transactions!$H$3:$J$9995, Transactions!$G$3:$G$9995=O2,"")
On the Transactions sheet in columns H-J I have Date, Source and Amount. In column G I have Category. I am trying to display the Date, Source and Amount for transactions that are categorized as "Utilities" In cell O2, I have "Utilities". For some reason, the filter function is not displaying all of the data that has been categorized as "Utilities". I am unsure what I have done wrong as I have double and triple checked that everything is spelled correctly and that all data is within the array.
- One problem I had with FILTER (and many other formulas) is the cell format/type. In my case, FILTER was not returning values when I used a test for the 'include' argument of FILTER (e.g., MyTable[myColumn] = 123456). In my case the number 123456 was not recognized by Excel as a number (the clue was that the value was aligned left in the cell), so I had to select the column values and use 'Data->Text to columns' to convert all values in the column to numbers. After doing so, FILTER worked fine.
- The first step in debugging might be to test the array relationship
=Transactions!$G$3:$G$9995=O2
or using a defined Name
= Category="Utilities"
Only if that works, is it worth going on to consider the formula
= FILTER(Date, Category="Utilities") Maybe the reason is "Utilities " with a space in the end like in cell G10 in the example. Because of this the row is not included in the filtered range. Otherwise can you attach your file or screenshots without sensitive data?
I checked that all category cells were the same. I even copied and pasted the cell with "Utilities" to make sure they were all the same but some of the rows are still not displaying with the filter. I am busy for the next few hours but will upload screenshots later.
