I have a spreadsheet where I need to calculate inventory and other data in different real estate markets based on data from the MLS. I'm looking for a way to calculate that information for several cities at once, rather than having to copy a query or a filter formula into every single cell.

It's my understanding that you can't make an array out of queries or filters, so I wanted to know if there was any sort of workaround that would allow for me to achieve the result I'm after in another way.

Here's a spreadsheet with some sample data: https://docs.google.com/spreadsheets/d/1SpO5hhcJgP2WU1JbjKJ9ljw-4PWMOViDyKGr9doxgzE/edit#gid=748192662

I wrote two formulas that work in single cells, but I'd like help with making one that would apply to all cells in a column at once.

My formulas below:

=counta(iferror(query('Raw Data'!A:D,"select A where B contains '"&A2&"'",0),))

=counta(iferror(filter('Raw Data'!B2:B,'Raw Data'!B2:B=regexreplace(A3,".*" & A3 & ".*",A3)),))

Please let me know if there's any method I can use to tackle this problem. Thank you!

CodePudding user response：

=ARRAYFORMULA(IF(A2:A<>"",COUNTIF('Raw Data'!$B$2:$B$11, A2:A), ""))

Paste this in B2