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

google sheets

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.

Best Answer

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

enter image description here

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

Related Topic