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

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

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

• I knew you'd come through with an answer. – ale Mar 15 '13 at 14:20
• So, I trust this is the answer, but I don't understand it. Can you explain? – Dorje Mar 15 '13 at 15:50
• Actually, I realize what happend. I need a formula for to replace the cell marked [=?] not a query that reformats the data. The data will be a long list of rows and I need a different order than what is show. I just used those as an example. – Dorje Mar 15 '13 at 15:55
• @Dorje Thanks for your feedback !! I've altered my answer as well. I hope this is the answer you're looking for. Have you noticed, both table are the same? – Jacob Jan Tuinstra Mar 15 '13 at 18:02
• This is it! Thanks so much for the answer and the care in explaining it. I appreciate it. – Dorje Mar 19 '13 at 13:30

Similar result - slightly different approach:

``````=ARRAYFORMULA(SUMIF('DATA'!\$A\$2:\$A&'DATA'!\$C\$2:\$C;\$A2&B\$1;'DATA'!\$B\$2:\$B))
``````
• -1; Your answer yields `0`, see last sheet in the example file I created. Perhaps you can enlighten us and show us show it works? – Jacob Jan Tuinstra Oct 17 '13 at 11:03
• Please take a look: link – Andreas Wurm Oct 17 '13 at 11:43
• The `QUERY` formula also calculates the unique entries, sums them up and presents the whole table. What's not fast about that? Look at the third sheet. – Jacob Jan Tuinstra Oct 17 '13 at 12:05
• I don't think that `QUERY` isn't doing a good job here. My wording was misleading... – Andreas Wurm Oct 17 '13 at 12:11
• Dorje wanted a formula to place in the cells like the one labeled [=?]. – Andreas Wurm Oct 17 '13 at 13:01

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.