Formula Excel Custom List

Asked

Viewed 398 times

1

Hello,

I’m creating a spreadsheet and I have a problem. Imagine there’s a tournament going on. 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 spreadsheet I have: Anderson - 10 Andrew - 14 Bruno - 15

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

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

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

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

From what I read about, I need a matrix formula that does this, to sweep the spreadsheet of the months, searching the names and adding up their points. but I couldn’t find anything close to my needs and I couldn’t do it on my own.

Someone could give me a light?

excel

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:

inserir a descrição da imagem aqui

Then, in each month cell, use the following formula and changing the name of the month according to the title of the month sheets (how it uses the $ to fix addresses that should not change, you can do to the line of the first name and then copy these cells to the other names):

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

This formula will search (through the function PROCV) the name of column A of sheet "Total" in column A of sheet A ("January", "February", etc.) and return the value of column B (the score). If not found, it returns 0 (guaranteed by using the function SEERRO). The parameter FALSO serves to indicate that the search must be exact.

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

The example I made when I was only three months old is available on 4shared.

  • Luiz, an excellent answer. I only have one question, I gave an example with 3 participants per month, but in fact I have about 100 to 150 participants per month, where some participate every month, others participate month or other, and others participate only once and do not return. Is there a way to automatically remove all names from the month of January, join with all names from February, join with all names from March and so he automatically create the list of names in the full tab? With this list of names I would apply the PROCV you suggested to me.

  • So in my head I don’t know (I need to think a little), but it should be possible to create a single list of names from the junction of several sheets using only formulas (since it seems to me your intention to avoid VBA...). But I suggest you open a specific question for this.

  • 1

    OK, Thank you Luiz.

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.