I'm using the FILTER function to pull a subset of data (e.g. F1) from a Table. I then pull a Filter again to show certain characteristics of the data (e.g. F2) to assist analysis. And then I want to count how many times the F2 combination shows up in F1.
F1 contains all of the data fields, while F2 is just 3 data fields
But I don't know how to reference F2 results and use that to count what matches in F1. I used to do this with SUMPRODUCT (with old menu-bar Filter).
Here's the old calculation: SUMPRODUCT((G$27=$CD$100:$CD$20000)*(G31=$CE$100:$CE$20000)*(H31=$CF$100:$CF$20000)) The criteria (G$27,G$31,H$31) or F2 is now in the spill array G29# and the range is now D$100# or F1.
But how do I reference these individual criteria within a spilled array and perform a boolean argument?
Thanks!
F1 contains all of the data fields, while F2 is just 3 data fields
But I don't know how to reference F2 results and use that to count what matches in F1. I used to do this with SUMPRODUCT (with old menu-bar Filter).
Here's the old calculation: SUMPRODUCT((G$27=$CD$100:$CD$20000)*(G31=$CE$100:$CE$20000)*(H31=$CF$100:$CF$20000)) The criteria (G$27,G$31,H$31) or F2 is now in the spill array G29# and the range is now D$100# or F1.
But how do I reference these individual criteria within a spilled array and perform a boolean argument?
Thanks!