Excel Column "SET Filter to " in one column count in another colu

[Airchief]

I am trying to set a COLUMN field name "U16' TO filter and count in another
column for a ref name. I want to program this in the cell to count for a
specific referee assigned to the "U16" age group . In a nutshell I need this
cell to tell me exactly how many games this ref is assigned to in a specific
age group.

HELP

Example
A B C D E F
Time Field Age Center Side1 Side 2
0900 1 U12 John Smith Joe stone Kasey
1000 3 U12 John Smith Kip Jones Dan
1400 4 U19 Sue HANDY John Smith Wayne

(End Product)
Name Age Center Side1 Side 1
John smith U12 2 0 0
John Smith U19 0 1 0

 

[Stefi]

(End Product)
Name Age Center Side1 Side 1
John smith U12 "formula"->
John Smith U19 V

=SUMPRODUCT(--($A12=D$2$4),--($B12=$C$2:$C$4))

Replace "formula" by this formula and fillit to the right and down!

Regards,
Stefi

 

[Airchief]

Stefi,
Open this screen wide...

I know you got me close with this array. Help me understand your formula.

What is A12 & B12?
Are you saying A12 Is "TIME" or "NAME"
IS B12 saying "Field" or "Age"

My coulumns are 3-73

A B C D E F G
H I
Time Field Age Home vs Visitor Ref
Side1 Side 2
9:00 AM Field #4 U-19 Keydets vs Vulcan Elton Joe
Suzy
9:00 AM Field #3 U10 United vs Blasters Mark Alex
Ricky
9:00 AM Field #2 U12 Arsenal vs Eagles Clayton Jake
James
9:00 AM Field #1 U-19 Crew vs Earthquakes Elton Forrest Eric

In a separate cell I want to be able to Sum the number of time that Elton
was a Ref for age group U-19. i am trying to sum up each age group for a
particular Ref so i can pay him. This is my actual spreadsheet. Thank you
Stefi.

 

[Stefi]

Sorry, I forgot to show the structure of my test sheet. It is like this:

A B C D E
F
1 Time Field Age Center Side1 Side2
2 900 1 U12 John Smith Joe stone Kasey
3 1000 3 U12 John Smith Kip Jones Dan
4 1400 4 U19 Sue HANDY John Smith Wayne
....
11 Name Age Center Side1 Side2
12 John Smith U12 2 0 0
13 John Smith U19 0 1 0

A12 means Name, B12 means Age.

Regarding your actual spreadsheet (say Sheet1), do you mean such a result?
You can set it up in a separate sheet (say Sheet2).

A B C D E
Name Age Ref Side1 Side2
Elton U10 0 0 0
Elton U12 0 0 0
Elton U19 2 0 0
Mark U10 1 0 0
Mark U12 0 0 0
Mark U19 0 0 0
Clayton U10 0 0 0
Clayton U12 1 0 0
Clayton U19 0 0 0

The formula in C2:
=SUMPRODUCT(--($A2=Sheet1!G$2:G$5),--($B2=Sheet1!$C$2:$C$5))

Regards,
Stefi


„Airchief†ezt írta: