Counting with a lot of criterias across multiple sheets

Hi there

I might as well start with stating the fact that I'm an Excel rookie, so I only know the most basic stuff, and what I could find (and understand...) on the internet. And I'm totally stuck on this one!

I have a workbook, where I'm trying to do some statistics for football officials. It consists of multiple sheets:
- The plan for which officials are assigned to which games
- Data from games on flags thrown (or not thrown)
- The sheet where I want to do the stats

The plan looks like this:
Plan.gif

The game data looks like this:
GameData.gif

The different official's positions are called R, U, H, L, F, S and B.
For every flag thrown in a game, the official can get different grades; CC, MC, NC, IC or NG.
The gradings are in columns E and H in the game data sheet. (for example RC is R getting a CC grade, UN is U getting a NG grade and so on. The NG grade gets the letter "G" (HG for H getting a NG grade for example) as N is already used for NC.)

My trouble is, that people are working different positions in different games. So when I want to count for example how many of the different grades KL has, I have to somehow cross check the data with the plan.

Example:

In game # 103040 KL is working the F position, but in game 103068 he is working the R position. When doing the stats for KL, I then have to first check in the plan what position he is working, before getting the grades in the data sheet. And I have no idea how I do that...

I have already done the grades per position, as there was no need to cross check with the plan, but on this one I'm totally stuck.

My plan was making a list like this:
Stats.gif

So I simply need to count the number of each grade for each official (the group they are listed under is their primary position, but everyone is working multiple positions during the season).

Can someone help me? I can provide a copy of the document if needed, but I have to make some changes to it first, as I can't put people's grades online (although it's not likely that anyone involved are looking in here )

Thank you for your fast answer. I'm not entirely sure what you mean though, the last column in the data sheet (column I) already has the game # in each row.

The data is imported from a web site, so the data sheet will be updates every week through the season. The website spits out a lot of data into an Excel sheet, and I remove the unneeded rows with a macro, and then copy/paste the data into the data sheet of the statistics document.

I have attached the document as it is at the moment.

ExcelForum_stats.xlsx

I just noticed that the document I uploaded is missing the projected results and the marking of which cells are result cells and which are data cells.... I attached a new document where the cells are colored and there is an explanation for each color. I hope it makes sense...

ExcelForum_stats_new.xlsx

In the attached file I have added a lot of helper columns to the data sheet. Column J matches the official's position to the person using the formula:

Please Login or Register  to view this content.

Column K matches the letter to the grade using the formula:

Please Login or Register  to view this content.

This pattern repeats through columns v and w. The formula for the results sheet is:

Please Login or Register  to view this content.

Copy of ExcelForum_stats_new.xlsx

Let me know if you have any questions.

This looks awesome! Thank you so much!

I'm playing around with moving the helper columns to it's own sheet, so it will be easier for me when I have to update the data sheet during the season, and it seems it's still working even though I don't fully understand the Index command :-)

I' am very very grateful for your help, and maybe I can learn something by studying the formulas :-) I understand the countifs formula, but I gotta study a bit on the index and match stuff - it seems it could be helpful in some other stuff I'm doing too :-)

Again, thank you so much!

You're welcome. Please use the thread tools above your first post to mark the thread as 'Solved'. Hope that you have a good day.