COUNT Games Played

[chester1993]

Good day to everyone! So, this will be my 1st post here is please bare with me.

Anyhow, I need your help. I am currently working on games stats. Apparently, I am having trouble with one thing. Here is my worksheet:

excel.png

Here's what I want to do:

1. I want to come up with a formula that can count how many games a certain player played. For instance: I want to appear on E2 how many times Drew played any game (which is in A2:A12).

2. I want to come up with a formula that can count how many times a player chose a certain game. For instance: I want to appear on E3 how many times Juls played Game 3(which is in A2:A12).

Another one:

excel2.png

3. I want to come up with a formula that would rank entries automatically. As you can see on the picture, I want to rank the entries from column F based from the numbers on column G. However I want it ranked on column B (for Column F) and column C (for Column G). Is this possible?


I am really looking forward for everyone's help!

[spitfireblue]

Hi,

Can you send through an attachment, I am having trouble viewing the pictures.

[chester1993]

Of course. Here it is.

[spitfireblue]

Here you go...

442941d1453879801-count-games-played-sample.xlsx

[chester1993]

Hey thanks man!

Geez, I didn't realized it was that easy. What a noob. One more question, well, more of a clarification.

For my second situation, you've given me this formula:

Please Login or Register  to view this content.

what does the 2,0 mean? Sorry for the very noob question, I just want to understand it better so I can use it further in the future! Thanks again for the help man!

[spitfireblue]

Hey, no problems

With a VLOOKUP it looks up a value (A20) in the left hand column (column E) and will return a value from the 2nd column in the range (i.e. from column F in this case). The 0 indicates an exact match.

If this doesn't make sense let me know.

[chester1993]

Still is confusing. Hahaha! Sorry mate!

I think I am almost there. The "2" is really confusing me. Hahaha!

Also, I want to clarify this formula too:

Please Login or Register  to view this content.

I understand how the RANK function works. I am not sure what's this fo:

Please Login or Register  to view this content.

Mind to enlighten me sir?

[chester1993]

OK. I kinda understand it now. So "2" means, the 2nd column in the table wherein the return would come from. In short, the value that would appear on the selected cell comes from that "2". My question now is, what if the value that I want is from the 1st column? I don't think "1" is the answer, is it?

So, let's put it this way:

The data that I want to lookup is in 1st column (meaning all the name of the games is in there), 2nd column would be the #of times played, and the 3rd column would be the rank.
On the 4th column, 1st cell, that's where I put my formula. How would that go?

Is it something like this?

Please Login or Register  to view this content.

I'm pretty sure that this is not the right formula for it, so please enlighten me.

[chester1993]

Ok. I finally did it. My problem now is, it's not RANKED.

I copied everything that you gave me, unfortunately, it's not ranked. Instead of giving me the ranked #1 on the list, it gives me the 1st value.

[spitfireblue]

Hi,

The COUNTIF formula is used to determine if their are any duplicate values in G and then forces the second one to be ranked second.
For example, Games 8 and 16 both have a value of 10. Therefore...
- The RANK formula will return a value of 1 (as it is the highest value)
- The COUNTIF formula will return a value of 1 for Game 8 and a value of 2 for Game 16
- Then you minus 1 off of that value
Game 8 = 1 + 1 - 1 (Ranked 1st) and Game 16 = 1 + 2 -1 (Ranked 2nd even though it has the same value as Game 8)

[spitfireblue]

You are on the right track with the VLOOKUP. If you want to use the value in the first column you can replace the 2 with a 1.
However in your example above it will return the value in cell A1, which I doubt will be very useful. What do you want to see in the fourth column?

[chester1993]

Alright. So, basically, the COUNTIF formula is a "tie-breaking" formula. Am I right?

[chester1993]

Let me give you another file. We'll be doing the same thing sir.

sample2.xlsx


So basically, I want to rank the data from cell B into cell P (if you notice they have the same header which is "banned") and so on so fort. can you help me out please? Maybe I could understand it better this time. Thanks and sorry!

[spitfireblue]

With a VLOOKUP, it looks up the value on the left hand column, so to use a VLOOKUP for the sample sheet you will have to have the rank formula to the left of the names.
A better way to do it is with an INDEX and MATCH formula.
MATCH can work out what row a value is in and INDEX will return the value in a certain row, so together these formula's are very useful like this...
Copy of sample2.xlsx
(I also included a tab of how you would do this with VLOOKUP)

[chester1993]

Wow. This is really awesome mate! For some reason I understand INDEX and MATCH better than VLOOKUP. Hahaha! But hey, let me examine all of this. I can't thank you enough for answering my questions. It really helped me a lot.

[micope21]

Hi

No need to Rank!!

Cell P6

Please Login or Register  to view this content.

Enter, then Copy down

See the files

Regard

[chester1993]

Hi

No need to Rank!!

Cell P6

Please Login or Register  to view this content.

Enter, then Copy down

See the files

Regard

Hi sir! Thanks for the response. I appreciate it.

Unfortunately, I don't understand a thing with the formula you gave me. It's new to me. But, please don't get me wrong, I still appreciate it. Would you mind explaining what happened there?

Also, the reason I want to rank it because I also want to see the accumulated amount of games played besides each entry. Would that also be possible with the formula you gave me?

[micope21]

Hi sir! Thanks for the response. I appreciate it.

Unfortunately, I don't understand a thing with the formula you gave me. It's new to me. But, please don't get me wrong, I still appreciate it. Would you mind explaining what happened there?

Also, the reason I want to rank it because I also want to see the accumulated amount of games played besides each entry. Would that also be possible with the formula you gave me?

You already got rank number 1 to 10 cell N6:N15, Q6:Q15, T6:T15 so on etc.
The formula I gave you, already rank for you without putting extra helper column formula like Spitfireblue did?

Regard

[Ronit26]

Hi Mate,
I've gone through all your conversation. This looks as interesting as my favorite Indian card games. I am also trying this for last one hour but still confused. I think it will take some more time to understand.

[RajGamer]

I play rummy all the time and this information is very valuable for me.