I have products as columns and companies as rows, the selections are shown with the checkboxes.
However I am not interested in querying companies, I want to query and count the products selections and display them like this:
What query formula should I used for this?
Try this, look at this Example Sheet
Paste this formula next to products name and drag down.
=COUNTIF(FILTER($B$2:$L$16,$B$1:$L$1=N2),TRUE)
FILTER the range $B$2:$L$16 where the headers $L$1 = the value in range N2:N12 products, the output is TRUE and FALSE Array.
count TRUE value in the Array resulted from FILTER with COUNTIF function COUNTIFFILTER[ True / false array ], TRUE TRUE is COUNTIF criterion.
=COUNTIF(FILTER(Selections!$B$2:$L$16,Selections!$B$1:$L$1=B2),TRUE)
At a glance
A tweek on player0 answer keep ranges open to add more values
=ARRAYFORMULA({FLATTEN(Selections!B1:L1),
FLATTEN(MMULT(TRANSPOSE(Selections!B2:L*1),
SEQUENCE(ROWS(Selections!B2:B), 1, 1, 0)))})
try:
=ARRAYFORMULA({FLATTEN(Selections!B1:L1),
FLATTEN(MMULT(TRANSPOSE(Selections!B2:L16*1),
SEQUENCE(ROWS(Selections!B2:B16), 1, 1, 0)))})
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.
