Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 9 down vote accepted

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)

share|improve this answer
3  
I knew you'd come through with an answer. –  Al E. 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
1  
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))
share|improve this answer
    
-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 –  Andi 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... –  Andi Oct 17 '13 at 12:11
    
Dorje wanted a formula to place in the cells like the one labeled [=?]. –  Andi Oct 17 '13 at 13:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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