Formula Excel Custom List

Navigation
1

Hello,

I'm creating a spreadsheet and I have a problem. Imagine that a tournament is taking place. To control this tournament I have several tabs, January, February, March, etc. Within each tab I have the names of the tournament participants, and how many points they made throughout that month. Not all players participate in the tournament every month.

In the January worksheet I have: Anderson - 10 André - 14 Bruno - 15

In the February worksheet I have: Anderson - 12 Bruno - 9 Carlos - 15

In the March worksheet I have: Anderson - 8 Carlos - 10 Daniel - 12

What I would like to do is, at the end of all tabs for each month, create a "Total" tab and gather the names of all participants. In this case: Anderson, André, Bruno, Carlos and Daniel

Adding all their respective points. In this case: Anderson - 30 Carlos - 25 Bruno - 24 André - 14 Daniel - 12

From what I read about, I need a matrix formula that does this, to scan the worksheet of the months, searching for the names and adding up their points. but I did not find anything approaching my needs and I could not do it on my own.

Could someone give me a light?

    
asked by anonymous 30.01.2015 / 22:42

1 answer

1

On the last sheet (called, for example, "Total"), create a column for each month, keeping the names of the participants in the first column. For example:

Thenineachcellofmonths,usethefollowingformulaandchangingthemonthnameaccordingtothetitleofthemonthssheets(howdoesituse$tosetaddressesthatshouldnotchange,youcandofortherowofthefirstnameandthencopythosecellstotheothernames):

=SEERRO(PROCV($A1;Janeiro!$A$1:$B$3;2;FALSO);0)

Thisformulawilllookfor(bymeansofthePROCVfunction)thecolumnnameAofthe"Total" sheet in column A of the given sheet ("January", "February", etc.) and return the value of column B (the score). If it does not find it, it returns 0 (guaranteed by using the SEERRO function). The FALSO parameter is used to indicate that the search has to be exact .

Well, having the values in the columns, just add them in a column "Total" (with formula =SOMA(B2:D2) , for example). If you do not want to see the columns of the months on this last sheet, just hide them (select the columns by the header, right-click and select "Hide").

The example I did only with three months is available in 4shared .

    
30.01.2015 / 23:17
NSObjectInaccessibleException - Deleting all objects from an entity - NSFetchedResultsController Call javascript onsubmit does not "trap" the submit until giving confirmation of eiliminação