Filter basic using # notation

[ValuationMan]

I really like using # to capture all of the cells in a spilled array. Let's say A1 contains the spilled array. I could easily do something like =COUNTIFS(array, A1#) and the COUNTIFS will do the formula for each of the cells in A1#. Is there someway to do that with filter? I'm trying to do something like =SUM( FILTER(Array, Array_Condition = A1#))

 

[StephenCrump]

The usual approach would be something like this:

=SUM(--ISNUMBER(MATCH(Array,A1#,)))

If you are determined to use the FILTER function, you'd need a more complicated construction. One possibility:

=LET(m,MATCH(Array,A1#,),COUNT(FILTER(m,ISNUMBER(m))))

 

[Fluff]

Can you post some sample data along with expected outcome.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.