GoogleDocs Spreadsheet Super Challenge!

November 26, 2008 8:11 AM

ExcelFilter: I need to make a cell in one column tell another cell in that row to add itself to another cell somewhere else entirely. Added difficulty! I need to do this in Google Docs.

I have a sheet where one column (column C) will only have five set text values. (And by set values I mean there will only ever be one of five things that are typed in a cell in this column. I don't mean that Excel knows what these values are.)

Whenever one of these five values are entered, there will also be a numerical value typed in a different cell in that row (always in column G). That numerical cell gets summed into a numerical cell on a different portion of the sheet.

What I'd like to do is have the sheet recognize when one of the five text values is typed into column C and have that trigger the sheet to automatically add whatever is in column G to the sum in the ensuing cell (H3 for our purposes here).

So, for example: cell C12 has "Graphics" typed into it, then "4" typed into cell G12. I would like G12 to know that it has to automatically add itself to the sum in cell H3 based on the fact that "Graphics" has been typed into C12.

Unfortunately, I need to do this in Google Docs' spreadsheet program. Is this even possible?

posted by greenland to computers & internet (3 comments total)

Use the SUMIF function in cell H3. For example =SUMIF(C4:C63355,"Graphics",G4:G65535) will summ all the cells from G4:G65535 where the corresponding value in column C is equal to "Graphics".
posted by reptile at 8:24 AM on November 26, 2008

reptile has it, except that the cell ranges should be of equal length. I would suggest using column references instead - provided there is no other data in those columns.

The formula in H3 would look like this, =SUMIF(C:C, "Graphics", G:G).
So if Graphics is entered into any cell in column C, the formula will sum the corresponding values in column G.

This should work in Excel and Google Docs, and is cleaner for Google Docs since, unlike Excel, their spreadsheets supposedly are not limited to 65536 rows.
posted by rube goldberg at 8:55 AM on November 26, 2008

Erm yeah 63355 was a typo, meant to put 65535. I blame lack of coffee. Good luck with your spreadsheet.
posted by reptile at 9:20 AM on November 26, 2008