So I have data in one sheet like this:

Pig         20     Y
Pig         30     N
Pig         10     Y
Cow         20     Y
Cow         30     N
Cow         50     N
Bat         10     N


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.


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.


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



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:


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


enter image description here


See example file I've prepared, where both samples are present: SUM data based on two variables


The formula in the example file is a bit different (the range is set to contain complete columns).

| improve this answer | |
  • 3
    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
  • 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:

| 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 – 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:


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


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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