Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Column "SET Filter to " in one column count in another
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" wrote: (End Product) Name Age Center Side1 Side 1 John smith U12 "formula"- John Smith U19 V =SUMPRODUCT(--($A12=D$2:D$4),--($B12=$C$2:$C$4)) Replace "formula" by this formula and fillit to the right and down! Regards, Stefi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Column "SET Filter to " in one column count in another
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: 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" wrote: (End Product) Name Age Center Side1 Side 1 John smith U12 "formula"- John Smith U19 V =SUMPRODUCT(--($A12=D$2:D$4),--($B12=$C$2:$C$4)) Replace "formula" by this formula and fillit to the right and down! Regards, Stefi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Pivot Table Data with Column "Sum" rather than "count" defa | Excel Discussion (Misc queries) | |||
Auto Filter - how to collapse "non-blank" cells in a column? | Excel Discussion (Misc queries) | |||
How do I change the column heading in Excel to display "A" "B" "C | New Users to Excel | |||
how can I count if column A="active" and column E="Job" in a list? | Excel Worksheet Functions | |||
how can I count if column A="active" and column E="Job"? | Excel Worksheet Functions |