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

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

Answer 2

Similar result - slightly different approach:

=ARRAYFORMULA(SUMIF('DATA'!$A$2:$A&'DATA'!$C$2:$C;$A2&B$1;'DATA'!$B$2:$B))

Answer 3

Assuming data is in Sheet DATA starting A1 and TYPE is in A1 of a different sheet, then in B2 copied across and the pair copied down:

=sumifs(DATA!$B:$B,DATA!$A:$A,$A2,DATA!$C:$C,left(B$1))

Depending upon Sheet's locale may require ;s instead of ,s.

SUMIFS
LEFT