Google-sheets – Spell check with formula or apps script

If you're thinking about "loop and if statements", you should look into Google Apps Scripts.

Other than that, it seems you're looking for the TRANSPOSE function, in conjunction with the FILTER function. Take a look at the Google Spreadsheets Function List.

If you can tell me the purpose of the Purchase column in your example, I might be able to set up a working example.

If you add the following formula in the second sheet (A1).

Formula

=QUERY(Sheet1!A1:C9;"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A PIVOT C")

then the table will appear as you want.

Explained

Here an explanation of the formula, from the inside to the outside:

• the PIVOT C transposes the unique results from column C (turning rows into columns)
• the GROUP BY A only filters out unique values in column A. (the same as sheet TABLE!A2)
• the WHERE B IS NOT NULL ignores empty cells
• the SUM(B) adds the result of the query
• the SELECT A simply displays column A, as unique values

If you're only interested in individual results, then I suggest using the following formula:

=IFERROR(SUM(FILTER(DATA!$B$2:B;DATA!$C$2:C=$B$1;DATA!$A$2:A=A2));"")

Here an explanation of the formula, from the inside to the outside:

• the FILTER function will retrieve the amounts for range B2:B, column B without the header, by filtering range B2:B for "No" and range C2:C for "Yes".
• the SUM function will add them together.
• the IFERROR will leave a blank cell if an error occurred (not result).

Screenshot

Example

See example file I've prepared, where both samples are present: SUM data based on two variables

Note

The formula in the example file is a bit different (the range is set to contain complete columns).