New answers tagged

0

In the formatting dialog, select "custom formula" with the formula = B1>=A1+2. That is, enter the inequality or equality you wish to trigger the formatting, preceded by = sign.


1

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 ...


1

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),"\")) } ) ),...


2

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 ...


0

may be you're looking for simple query formula: =query({Sheet1!A:C},"select Col1, Count(Col3) where Col1 <> '' group by Col1 pivot Col2",1) I used Col3 and range A:C here in order to be able to pivot column B (Products). More info here.


1

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.



Top 50 recent answers are included