using a QUERY formulate in Google Spreadsheet

March 22, 2010 11:17 AM

Hi, I searched through the archives and did not find a match. Thank you in advance. Let's say I have a spreadsheet in Google docs with two columns, names and dollar amounts. Jane Smith, $5; Joe Sixpack, $7; Jane Goodall, $4; Joe Frankin, $10, etc. I want to use – I think – the QUERY function to ADD up all the dollars that all the Joe's have, or all the dollars that the Jane's have. I have read through as much documentation as I can find and it's not giving me enough to formulate a successful query. This:

=query(A1:B20, "Select A where (A contains 'jane')", sum(B))

does not work. Any suggestions? thank you!
posted by noway to Computers & Internet (11 answers total)

The third argument is not a predicate; it is the number of header rows you want use in your new data table. What you want is "select sum(B) where A contains 'jane'" in the second argument.
posted by mkb at 11:33 AM on March 22, 2010

ah, thank you mkb, the structure lesson is very useful. i must be doing it wrong, though, as the output of that query is now just the word "sum." I've not seen that before. :-) thanks again!
posted by noway at 11:55 AM on March 22, 2010

What I do is make a 3rd column and use an 'if' formula to copy the money amount over only if the 1st column matches "joe". Then sum that new column.

Not a direct answer, I know, but that's how I do it . . .
posted by flug at 12:34 PM on March 22, 2010

flug, that's a good idea! i'll totally try that, as well. thank you.
posted by noway at 12:40 PM on March 22, 2010

I would do it this way:
=SUMIF(A1:A20, "jane",B1:B20)

where A1:A20 is the range with the names and B1:B20 is the range with the dollar amounts.
posted by beyond_pink at 12:43 PM on March 22, 2010

hi beyond_pink, that's a very concise formula, thank you! but does that "if" do partial matches? it appears to be looking for an absolute match for me. thanks, again!
posted by noway at 1:11 PM on March 22, 2010

Ah! Sorry for misreading your question. To do a partial match, you could use a * as a wild card:

=SUMIF(A1:A20, "Jane*", B1:B20)
posted by beyond_pink at 1:34 PM on March 22, 2010

And if you are looking for Jane anywhere in the cell (not just the beginning), you can put a * on either side of Jane:

=SUMIF(A1:A20, "*Jane*", B1:B20)
posted by beyond_pink at 1:35 PM on March 22, 2010

hi beyond_pink. alas, it appears wildcards do not work in google docs. (!)

http://groups.google.com/group/How-to-Documents/browse_thread/thread/554b8f0c3af7e437

:-(
posted by noway at 2:18 PM on March 22, 2010

i think there may have been something wrong with my table because after tweaking the range I got mkb's answer to work. thanks everyone!
posted by noway at 2:33 PM on March 22, 2010

correction, it wasn't the range I needed to tweak, it was the pattern being matched. apparently, it's case sensitive.
posted by noway at 2:40 PM on March 22, 2010