counting instances

**bzl** · 08-02-2014, 08:37 AM

Hi, I have attached a spreadsheet with sample data. In column A I have a list of players, in column B I have what game they have played in. I did a formula to try and identify if the player was in game 1 and game 3.
The formula is =COUNTIFS($A$2:A2,A2,$B$2:B2,"game 1")+COUNTIFS($A$2:A2,A2,$B$2:B2,"game 3"). The formula half works as it does identify the player against game 3 with the result of 2. I would like to see the result of "yes" or something similar so I can filter on the final results to bring up game 1 and game 3 for the player.

**boopathiraja** · 08-02-2014, 08:46 AM

IF(COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,"game 1")+COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,"game 3")>1,"Yes","")

**bzl** · 08-02-2014, 08:54 AM

Thanks Boopathiraha, that is what I want however it shows game 2 as well with "yes", which I was trying to avoid. I only want to see game 1 and game 3. Is there a way to avoid this?

**AlKey** · 08-02-2014, 09:59 AM

Maybe there is a better way but try this one

=IF(SUM(COUNTIFS($A$2:$A$11,A2,B$2:B$11,{"game 1","game 3"}))-IF(B2="game 2",1,0)>1,"Yes","")

**boopathiraja** · 08-02-2014, 10:15 AM

May be try this

IF(OR(B2="game 1",B2="game 3"),IF(AND(COUNTIFS($A$1:$A$11,A2,$B$1:$B$11,"game 1")>=1,(COUNTIFS($A$1:$A$11,A2,$B$1:$B$11,"game 3")>=1)),"Yes",""),"")

**bzl** · 08-02-2014, 10:44 AM

Thank you both, Alkey and Boopathiraha. Two good formulas that I can use...I can finally go to sleep now... Thanks again...

**martindwilson** · 08-02-2014, 10:50 AM

bit of a typo there alkey
=IF(SUM(COUNTIFS($A$2:$A$11,A2,$B$2:B$2:B$11,{"game 1","game 3"}))-IF(B2="game 2",1,0)>1,"Yes","")

**newdoverman** · 08-02-2014, 10:52 AM

Perhaps this will satisfy the problem. Nothing is indicated unless Game 1 and Game 3 have been played. Game 1 has been left blank because as of that time, Game 3 has not been played. Therefore, the result is beside Game 3 if the conditions have been satisfied.

Formula:

Please Login or Register  to view this content.

**AlKey** · 08-02-2014, 11:14 AM

Originally Posted by martindwilson

bit of a typo there alkey
=IF(SUM(COUNTIFS($A$2:$A$11,A2,$B$2:B$2:B$11,{"game 1","game 3"}))-IF(B2="game 2",1,0)>1,"Yes","")

Thanks Martin. It is now corrected

Good thing it did not effected calculations.

counting instances

LinkBack

Thread Tools

Rate This Thread

Display

counting instances

Re: counting instances

Re: counting instances

Re: counting instances

Re: counting instances

Re: counting instances

Re: counting instances

Re: counting instances

Re: counting instances

Thread Information

Users Browsing this Thread

Similar Threads

Counting Instances of Sick

Counting instances in a filter

Counting the number of instances

Counting instances of an offset value???

counting instances of decimals

Bookmarks

Bookmarks

Posting Permissions