I have products as columns and companies as rows, the selections are shown with the checkboxes.

Explanation

At a glance

A tweek on

CodePudding user response：

If the selection of prod 01 always reside in B2:B, why not just countif(B2:B, true) for prod 01?

For a spread-able formula, we note that you need the column index to iterate over different prod ##, but you want a summary table that is vertical. You can use indirect() to accomplish "transposed" iteration as follows. In the A1 of your other sheet, you can put

=countif(indirect("Sheet1!R2C"&(row(A1)-row(A$1) 1)&":C"&(row(A1)-row(A$1) 1),false),false) 

And you can spread vertically for the formula to retrieve counts in the subsequent columns in Sheet1.

If you move the starting cell of your summary table elsewhere, you should replace A1, A$1 in the above formula to the corresponding cell name.

The benefit of the above formula is that you are not scanning your data range unnecessarily.

Since we are mutating the formula in place, we only apply countif() to one specified column of your data at a time. For an m-by-n array, you are only doing atomic computations ~m*n times.

If you filter() your whole range per row, you multiple the amount of computations by n-fold.

If you centralize your formula by using mmult(), you multiply the amount of computations by m*n-fold -- squaring the original demand (and then scaled by a constant factor). With that, your sheet will be impractically slow way sooner than you may expect with mere a few thousand entries. Memory access would contribute to the run time delay.

Whenever you are handling a large database, only simplify your formula sparingly and cautiously.