Count # of cells based on multiple Criteria

Hello,

I am having issues using IF and COUNT statements. What I am trying to do is use the Player Stats tab as a raw overview screen which sums together the values in the Raw Data tab.

My current question is for the Games Played column in the This Tournament section of the Players tab. The logic I am going with is:
On the Raw Data tab, If tournament# = 1 and If Player Name in column B or C matches the name from column A on the Player Stats tab
Then count for that row under columns E through J on the Raw Data tab, the number of cells with a value >=21

The idea is to repeat this logic across all rows against the raw data tab, based on the attached file, I would expect the count for Billy to appear as 3. I feel like I am missing something very simple.

Thank you in advanced for your help. I am looking forward to your responses.

test file.xlsx

Originally Posted by TMS

You don't say in your profile what version of Excel you are using. But, on the basis of the file extension, I assume it is 2007+.

In that case, you should use COUNTIFS, not IF and COUNTIF

Regards, TMS

My apologies. I updated my profile to reflect the current version. I am using excel 2013.

When using the COUNTIFS, am I able to reference another cell in the criteria section? I was able to get the first criteria to work, however the second one is producing a #Value.

=COUNTIFS('Raw Data'!A2:A99999, "=1",'Raw Data'!B2:C99999, "='Player Stats'!A3")

I get the same #VALUE! result. In addition I tried it with a = inside the "" as well. I attempted that based on Microsoft's page. It seems like I am missing something simple.
http://office.microsoft.com/en-us/ex...010047494.aspx

Actually I just may have figured it out. The COUNTIFS cannot expand over multiple columns. In the second criteria I had it matching B and C. I guess I will have to create multiple countifs to resolve. I will post again if I hit more issues.

Thanks everyone for your responses. It has helped me populate a great portion of the excel statistics, however I have hit another road block. Please find the updated file.

test file2.xlsx

I am currently stuck on column I of the Player Stats tab. The logic I currently have is as follows:

=COUNTIFS('Raw Data'!$B$2:$B$99999,$P$1,'Raw Data'!$C$2:$C$99999,A3,'Raw Data'!$F$2:$F$99999,">=21",'Raw Data'!$F$2:$F$99999,">" & 'Raw Data'!$G$2:$G$99999)
+COUNTIFS('Raw Data'!$B$2:$B$99999,$P$1,'Raw Data'!$C$2:$C$99999,A3,'Raw Data'!$H$2:$H$99999,">=21",'Raw Data'!$H$2:$H$99999,">" & 'Raw Data'!$I$2:$I$99999)
+COUNTIFS('Raw Data'!$B$2:$B$99999,$P$1,'Raw Data'!$C$2:$C$99999,A3,'Raw Data'!$J$2:$J$99999,">=21",'Raw Data'!$J$2:$J$99999,">" & 'Raw Data'!$K$2:$K$99999)
+COUNTIFS('Raw Data'!$B$2:$B$99999,$P$1,'Raw Data'!$D$2:$D$99999,A3,'Raw Data'!$G$2:$G$99999,">=21,'Raw Data'!$G$2:$G$99999,">" & 'Raw Data'!$F$2:$F$99999")
+COUNTIFS('Raw Data'!$B$2:$B$99999,$P$1,'Raw Data'!$D$2:$D$99999,A3,'Raw Data'!$I$2:$I$99999,">=21",'Raw Data'!$I$2:$I$99999,">" & 'Raw Data'!$H$2:$H$99999)
+COUNTIFS('Raw Data'!$B$2:$B$99999,$P$1,'Raw Data'!$D$2:$D$99999,A3,'Raw Data'!$K$2:$K$99999,">=21,'Raw Data'!$K$2:$K$99999,">" & 'Raw Data'!$J$J:$H$99999")

Although this seems daunting, it mainly works, however the 4th criteria definition where I am attempting to compare two identical ranges is where all results end up as 0. The logic of each step is:

Criteria 1 = Is the tournament the current tournament, controlled by column B
Criteria 2 = Does the player name in column C or D in the raw data section match the player name on the players tab
Note: If player name is in column C, then that players scores would be entered in F, H, J
If player name is in column D, then that player's scores would be entered in G, I, K
Criteria 3 = Is the corresponding cell >=21
These first three criteria work, however I need a 4th criteria that takes into account if a score goes into extra points in which case, the value in each column need to be compared. This leads to Criteria 4. Columns F and G is game 1. H and I is game 2, and J and K is game 3.

Criteria 4 = The value in one cell must be larger than another cell.
For example, the 'Raw Data'!$I$2:$I$99999,">" & 'Raw Data'!$H$2:$H$99999) is stating (I think), for each line, if cell I# > than cell H# then count.

However my logic for Criteria 4 always returns a result of zero which is incorrect. Instead BK should return a games won result of 1 and DG should return a result of 2.

Thanks so much for taking the time to look into this!

Thanks Tony. That solved it. What I did was created a variance section and then used a value of ><0 based on the criteria I was looking for to pull in the data needed.

Thank you to everyone who help. I will make this as resolved.