How Do I Create a Google Spreadsheet Formula to SUM data based on two variables

Question

DATA SECTION:

So I have data in one sheet like this:

TYPE    AMOUNT     YES/NO
Pig         20     Y
Pig         30     N
Pig         10     Y
Cow         20     Y
Cow         30     N
Cow         50     N
Bat         10     N

RESULT SECTION

I'm using Google Spreadsheets and have a table in another sheet. I would like to have a formula (placed in the cells like the one labeled [=?]) that gives me sums like thus:

TYPE        YES      NO
Bat         [=?]    [=?]
Cow         20      80
Pig         30      30

But I still need the formula.

PROBLEM SECTION

I'm struggling. I want a formula in the results table that looks up in the data section for a match in type and SUMs the Ys for that type. I would like another formula that looks up in the data section for a match in type and SUMs the Ns for that type. I assume VLOOKUP or HLOOKUP, but I'm struggling getting the SUM in addition. I linked an example sheet here.

ADDITIONAL EDITS AFTER THE POST

I don't need a query. I just need a formula that replaces the [=?] for each column in the result section. I just put a ton of info there as an example.

Answer 1

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

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

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

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)