7

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.

3 Answers 3

12

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

7
  • 3
    I knew you'd come through with an answer.
    – ale
    Mar 15, 2013 at 14:20
  • So, I trust this is the answer, but I don't understand it. Can you explain?
    – Dorje
    Mar 15, 2013 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, 2013 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? Mar 15, 2013 at 18:02
  • 1
    This is it! Thanks so much for the answer and the care in explaining it. I appreciate it.
    – Dorje
    Mar 19, 2013 at 13:30
0

Similar result - slightly different approach:

=ARRAYFORMULA(SUMIF('DATA'!$A$2:$A&'DATA'!$C$2:$C;$A2&B$1;'DATA'!$B$2:$B))
5
  • -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? Oct 17, 2013 at 11:03
  • Please take a look: link Oct 17, 2013 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. Oct 17, 2013 at 12:05
  • I don't think that QUERY isn't doing a good job here. My wording was misleading... Oct 17, 2013 at 12:11
  • Dorje wanted a formula to place in the cells like the one labeled [=?]. Oct 17, 2013 at 13:01
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.