Solved Google Docs - Sum data from multiple Sheets

July 13, 2010 at 03:49:45
Specs: Windows 7

Hi,

I'm trying to work on a GoogleDocs Excel type spreadsheet and am a little stuck with a calculation. (It does seem to be able to do most of the things that Excel can do, but not everything.)

I'm trying to have one sheet for each month of the year and another "main" sheet.

Each monthly sheet pulls the users from the main sheet, and shows the points that each user has accrued for the month. The method that points are added to the sheet is by pasting the result of a script - but as sometimes there are new users from one month to the next, and some users aren't on the subsequent month, this isn't likely to be in the same order as the previous month/s...

What I'm looking to do is have the 'main' sheet review the other sheets and add all of their points into there, so that they can see where they stand in the group.

Here's an excerpt of the headers from the 'main' sheet, in case it helps:
Username | Rank | Points

I might also want to sort out a %age change from one month to the next, but I know how to do that already...

I was thinking about doing a DSUM type thing, but considering that there are hundreds of users this doesn't seem like it would work without a lot of manual adjustments - and then I was thinking about the VLOOKUP formula instead, but IDK how to put that...


Any help gratefully accepted..!


See More: Google Docs - Sum data from multiple Sheets

Report •


✔ Best Answer
August 24, 2010 at 07:18:01

Hi,

As you have the data in the order Name ... Nickname, VLOOKUP will not work for returning a Name for a given Nickname.

VLOOKUP always looks for a match in the first column of the lookup range and returns a result from a column to the right.

You could try a combination of Match and Offset
=OFFSET(Sheet1!$C$1,MATCH(B7,Sheet1!$C$2:$C$61,0),-1)

Match returns the number of rows down the list that it finds a match, and then this number is used as a row offset, for the Offset function, and -1 is the column Offset.

If there is no match, then you get an #NA error, so use this:
=IF(ISNA(MATCH(B7,Sheet1!$C$2:$C$61,0)),"No match",OFFSET(Sheet1!$C$1,MATCH(B7,Sheet1!$C$2:$C$61,0),-1))

Regards



#1
July 15, 2010 at 06:08:27

Hi,

Knowing nothing about the Google docs spreadsheet, but based on your comment that it's like Excel:

If you have created and maintain a full list of usernames on the sheet named "main", and the names are in column A, starting at A2,
and if your 12 monthly sheets are named "M1", "M2" etc.

Then in cell B2 this formula will add all the points for the person named in cell A2:
You will need to extend this to all 12 months - I just did a few as an example
=SUMIF('M1'!$A$2:$A$2000,A2,'M1'!$B$2:$B$2000) +SUMIF('M2'!$A$2:$A$2000,A2,'M2'!$B$2:$B$2000) +SUMIF('M3'!$A$2:$A$2000,A2,'M3'!$B$2:$B$2000) +SUMIF('M4'!$A$2:$A$2000,A2,'M4'!$B$2:$B$2000) +SUMIF('M5'!$A$2:$A$2000,A2,'M5'!$B$2:$B$2000)
Change the range on the monthly sheets to match the actual columns of names and points, and the likely number of rows that will be used.
When correct, drag the formula down on the main sheet alongside all the usernames.

I looked at Google docs spreadsheet help and SUMIF is a function that is available, and different sheets are referenced using the ! character, as in Excel. Single quotes around the sheet names seems to be acceptable - and required if there is a space in the sheet name.

(The order of usernames on the monthly sheets does not matter)

I note that you mention DSUM - DSUM is the equivalent of SUM (in Excel) when you have setup a database - it's a particular structure in Excel and DSUM should only be used with the database structure (there are other database equivalents such as DCOUNT in place of COUNT). the database equivalents require additional parameters such as field.

Regards


Report •

#2
July 16, 2010 at 01:17:14

Hi,

Thanks for that, I think I forgot to mention that I've already got a column on the 'main' sheet that added the totals from the other sheets. I've modifried the formula you gave to:

=SUMIF(Jul_10!$B$2:$B$750,B2,Jul_10!$C$2:$C$750)

Above is for the month of July (in case you were curious), but I've adjusted it to suit each of the other months too. Users are listed in column C on the main sheet, and in B on the others*. Works a charm.. :)

Next up, if I can be bothered would be to generate a chart to show the overall/combined changes for the total number of points.

I had a bit of a mission getting the count function to work on the users list, but that's obviously something within GoogleDocs as when I downloaded a copy it worked fine in Excel (2007) - but it doesn't really make too much difference, as we can just use the row number..


Thanks for your help.!

* = I've had to delete what was column A on the other sheets (a count of the number of users, but was blank if there was no user) in order to reduce the number of formulae on the other sheets, as it took me to the limit of formulae that GoogleDocs will allow you to use on a spreadsheet... LOL


Report •

#3
August 24, 2010 at 04:21:28

The previous response (from Humar) worked a treat, albeit with a few minor modifications that they wouldn't have know about...

I have stumbled on a slightly different (one-off) issue now, where I need to be able to to get the user's name from their nickname, but I can't get the VLOOKUP to work properly. I can get it to bring up their username if I put their username (not very useful) and I can get their nickname if I put in their username, but I'm trying to use get the username from the nickname.....and that's in both GoogleDocs and Excel..

Columns are as follows, just to save confusion:
Monthly sheet, where the formula will be:
Name | Nickname

Main sheet, where the master list of users is:
[blank] | Name | Nickname

So far, the closest I have been able to get, in my Excel test spreadie is:
=VLOOKUP(B7,Sheet1!$B$2:$D$61,1)
[[B7 = random user's name, result: same user's name - in this example, that is User7]]

or:
=VLOOKUP(B9,Sheet1!$B$2:$D$61,2)
[[B9 = random user's name, result: same user's nickname - in this example, that is also User7]]

Unfortunately those don't work, but it is at least a little more useful than the result of either of the following formulae - which just bring up "#N/A":
=VLOOKUP(B6,Sheet1!$B$2:$D$61,1)
[[B6 = random user's nickname - in this example, that is Nick3]]

or:
=VLOOKUP(B8,Sheet1!$B$2:$D$61,2)
[[B8 = random user's nickname - in this example, that is Nick3]]


I did try to make the search area wider, to try to get it to pick up the correct column, but that just seems to have thrown an error straight off the bat..


From this VLOOKUP example page ( http://office.microsoft.com/en-us/e... ) I'm trying to get the formula in Column A to pick up the adjacent cell's entry (Column B) and find it in the other sheet, then give me the correlating Username for that....but I can't manage to tweak the formula/e to get it to work..


Anyone here have any suggestions..?


Report •

Related Solutions

#4
August 24, 2010 at 07:18:01
✔ Best Answer

Hi,

As you have the data in the order Name ... Nickname, VLOOKUP will not work for returning a Name for a given Nickname.

VLOOKUP always looks for a match in the first column of the lookup range and returns a result from a column to the right.

You could try a combination of Match and Offset
=OFFSET(Sheet1!$C$1,MATCH(B7,Sheet1!$C$2:$C$61,0),-1)

Match returns the number of rows down the list that it finds a match, and then this number is used as a row offset, for the Offset function, and -1 is the column Offset.

If there is no match, then you get an #NA error, so use this:
=IF(ISNA(MATCH(B7,Sheet1!$C$2:$C$61,0)),"No match",OFFSET(Sheet1!$C$1,MATCH(B7,Sheet1!$C$2:$C$61,0),-1))

Regards


Report •

#5
August 24, 2010 at 07:33:27

BRILLIANT..!!

With a bit of minor tweaking, to suit the actual columns in the GDocs spreadie, it worked perfectly..!

Here is the final version:
=OFFSET('Master Sheet'!$C$1,MATCH(B2,'Master Sheet'!$C$2:$C$582,0),-1)


Thank you so much.!


Report •


Ask Question