Hot answers tagged


The following formula counts the rows of Data sheet which contain the string given in cell A1 of current sheet: =countunique(arrayformula(if(Data!$C2:$F = A1, row(Data!$C2:$F), ))) Explanation: the arrayformula replaces each matching string with the row number, and leaves other cells blank. Then countunique counts the number of distinct entries within ...


If somebody likes monster-formulas, here's single formula solution: =TRANSPOSE( QUERY( UNIQUE( ArrayFormula( { TRANSPOSE(SPLIT(CONCATENATE("\"&FILTER(Data!C2:F,Data!C2:C<>"")),"\")), TRANSPOSE(SPLIT(CONCATENATE("\"&row(offset(Data!C2,,,counta(Data!C2:C)))*COLUMN(Data!C2:F2)^0),"\")) } ) ),...


The data summary that you are trying to build, some people call them "crosstab report" or "pivot table". Google Sheets, have at least two easier than the complex formula in the question ways: QUERY function using the GROUP BY and PIVOT keywords. See QUERY for details. Pivot Table report. See How to create a pivot table report for details.


To illustrate the usage of Apps Script for such purpose, I wrote a function that sums selected values in a document: either in a table, or in a paragraph, or in several paragraphs, etc. It is invoked by menu command "Sum selected", found under "Custom"; this menu item is created when the document is opened. The text of election is split by whitespace, and ...

Only top voted, non community-wiki answers of a minimum length are eligible