| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array using 2 columns
Help
Open this screen wide... I know I can do this with aray. Can anyone help me?? 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Array using 2 columns
On 26 Maj, 19:31, Airchief wrote:
Help Open this screen wide... *I know I can do this with aray. Can anyone help me?? 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 Hi This should do it =SUMPRODUCT((C3:C73="U-19")*1,(G3:G73="Elton")*1) Regards, Per |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Array using 2 columns
I modified it a little and it worked GREAT!. Now I wanted to expanded the
second part to =SUMPRODUCT((C6:C73="U-19")*1,(J6:K73="Elton S")*1) but it won't allow me to do that. i get a #value error. Is it because i am trying to do 2 columns??? The J6:K73 is the error. can you help?? "Per Jessen" wrote: On 26 Maj, 19:31, Airchief wrote: Help Open this screen wide... I know I can do this with aray. Can anyone help me?? 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 Hi This should do it =SUMPRODUCT((C3:C73="U-19")*1,(G3:G73="Elton")*1) Regards, Per |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Array using 2 columns
I'm not sure what happens if J6 and K6 are both "Elton S" (and C6="U-19"), but
maybe one of these: Counts it twice: =SUMPRODUCT((C6:C73="U-19")*(J6:K73="Elton S")) or Counts it as one: =SUMPRODUCT((C6:C73="U-19")*(((J6:J73="Elton S")+(K6:K73="Elton S"))0)) Airchief wrote: I modified it a little and it worked GREAT!. Now I wanted to expanded the second part to =SUMPRODUCT((C6:C73="U-19")*1,(J6:K73="Elton S")*1) but it won't allow me to do that. i get a #value error. Is it because i am trying to do 2 columns??? The J6:K73 is the error. can you help?? "Per Jessen" wrote: On 26 Maj, 19:31, Airchief wrote: Help Open this screen wide... I know I can do this with aray. Can anyone help me?? 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 Hi This should do it =SUMPRODUCT((C3:C73="U-19")*1,(G3:G73="Elton")*1) Regards, Per -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Array using 2 columns
Hi
You can only use one column. Try this instead: =SUMPRODUCT((C6:C73="U-19")*1,(J6:J73="Elton S")*1)+SUMPRODUCT((C6:C73="U-19")*1,(K6:K73="Elton S")*1) Regards, Per On 26 Maj, 23:03, Airchief wrote: I modified it a little and it worked GREAT!. Now I wanted to expanded the second part to =SUMPRODUCT((C6:C73="U-19")*1,(J6:K73="Elton S")*1) but it won't allow me to do that. i get a #value error. Is it because i am trying to do 2 columns??? * The J6:K73 is the error. can you help?? "Per Jessen" wrote: On 26 Maj, 19:31, Airchief wrote: Help Open this screen wide... *I know I can do this with aray. Can anyone help me?? 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 Hi This should do it =SUMPRODUCT((C3:C73="U-19")*1,(G3:G73="Elton")*1) Regards, Per- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Array using 2 columns
Per
I got the same #value erros mess. I tried several thing but same erro. "Per Jessen" wrote: Hi You can only use one column. Try this instead: =SUMPRODUCT((C6:C73="U-19")*1,(J6:J73="Elton S")*1)+SUMPRODUCT((C6:C73="U-19")*1,(K6:K73="Elton S")*1) Regards, Per On 26 Maj, 23:03, Airchief wrote: I modified it a little and it worked GREAT!. Now I wanted to expanded the second part to =SUMPRODUCT((C6:C73="U-19")*1,(J6:K73="Elton S")*1) but it won't allow me to do that. i get a #value error. Is it because i am trying to do 2 columns??? The J6:K73 is the error. can you help?? "Per Jessen" wrote: On 26 Maj, 19:31, Airchief wrote: Help Open this screen wide... I know I can do this with aray. Can anyone help me?? 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 Hi This should do it =SUMPRODUCT((C3:C73="U-19")*1,(G3:G73="Elton")*1) Regards, Per- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Array using 2 columns
Do you have any errors in those ranges?
Airchief wrote: Per I got the same #value erros mess. I tried several thing but same erro. "Per Jessen" wrote: Hi You can only use one column. Try this instead: =SUMPRODUCT((C6:C73="U-19")*1,(J6:J73="Elton S")*1)+SUMPRODUCT((C6:C73="U-19")*1,(K6:K73="Elton S")*1) Regards, Per On 26 Maj, 23:03, Airchief wrote: I modified it a little and it worked GREAT!. Now I wanted to expanded the second part to =SUMPRODUCT((C6:C73="U-19")*1,(J6:K73="Elton S")*1) but it won't allow me to do that. i get a #value error. Is it because i am trying to do 2 columns??? The J6:K73 is the error. can you help?? "Per Jessen" wrote: On 26 Maj, 19:31, Airchief wrote: Help Open this screen wide... I know I can do this with aray. Can anyone help me?? 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 Hi This should do it =SUMPRODUCT((C3:C73="U-19")*1,(G3:G73="Elton")*1) Regards, Per- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Array using 2 columns
Dave,
I use this formula you gave me and seems to work.. =SUMPRODUCT((C6:C73="U-19")*1,(J6:J73="Elton S")*1)+SUMPRODUCT((C6:C73="U-19")*1,(K6:K73="Elton S")*1) "Dave Peterson" wrote: Do you have any errors in those ranges? Airchief wrote: Per I got the same #value erros mess. I tried several thing but same erro. "Per Jessen" wrote: Hi You can only use one column. Try this instead: =SUMPRODUCT((C6:C73="U-19")*1,(J6:J73="Elton S")*1)+SUMPRODUCT((C6:C73="U-19")*1,(K6:K73="Elton S")*1) Regards, Per On 26 Maj, 23:03, Airchief wrote: I modified it a little and it worked GREAT!. Now I wanted to expanded the second part to =SUMPRODUCT((C6:C73="U-19")*1,(J6:K73="Elton S")*1) but it won't allow me to do that. i get a #value error. Is it because i am trying to do 2 columns??? The J6:K73 is the error. can you help?? "Per Jessen" wrote: On 26 Maj, 19:31, Airchief wrote: Help Open this screen wide... I know I can do this with aray. Can anyone help me?? 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 Hi This should do it =SUMPRODUCT((C3:C73="U-19")*1,(G3:G73="Elton")*1) Regards, Per- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Array using 2 columns
That wasn't the formula I suggested.
Both the suggestions that I made worked ok for me. Airchief wrote: Dave, I use this formula you gave me and seems to work.. =SUMPRODUCT((C6:C73="U-19")*1,(J6:J73="Elton S")*1)+SUMPRODUCT((C6:C73="U-19")*1,(K6:K73="Elton S")*1) "Dave Peterson" wrote: Do you have any errors in those ranges? Airchief wrote: Per I got the same #value erros mess. I tried several thing but same erro. "Per Jessen" wrote: Hi You can only use one column. Try this instead: =SUMPRODUCT((C6:C73="U-19")*1,(J6:J73="Elton S")*1)+SUMPRODUCT((C6:C73="U-19")*1,(K6:K73="Elton S")*1) Regards, Per On 26 Maj, 23:03, Airchief wrote: I modified it a little and it worked GREAT!. Now I wanted to expanded the second part to =SUMPRODUCT((C6:C73="U-19")*1,(J6:K73="Elton S")*1) but it won't allow me to do that. i get a #value error. Is it because i am trying to do 2 columns??? The J6:K73 is the error. can you help?? "Per Jessen" wrote: On 26 Maj, 19:31, Airchief wrote: Help Open this screen wide... I know I can do this with aray. Can anyone help me?? 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 Hi This should do it =SUMPRODUCT((C3:C73="U-19")*1,(G3:G73="Elton")*1) Regards, Per- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Array using 2 columns
All I can say is MANY THANK YOUs. I have tried all week to get it to work and
you gave me the formula to make it work. I thank you so MUCH!!! "Dave Peterson" wrote: That wasn't the formula I suggested. Both the suggestions that I made worked ok for me. Airchief wrote: Dave, I use this formula you gave me and seems to work.. =SUMPRODUCT((C6:C73="U-19")*1,(J6:J73="Elton S")*1)+SUMPRODUCT((C6:C73="U-19")*1,(K6:K73="Elton S")*1) "Dave Peterson" wrote: Do you have any errors in those ranges? Airchief wrote: Per I got the same #value erros mess. I tried several thing but same erro. "Per Jessen" wrote: Hi You can only use one column. Try this instead: =SUMPRODUCT((C6:C73="U-19")*1,(J6:J73="Elton S")*1)+SUMPRODUCT((C6:C73="U-19")*1,(K6:K73="Elton S")*1) Regards, Per On 26 Maj, 23:03, Airchief wrote: I modified it a little and it worked GREAT!. Now I wanted to expanded the second part to =SUMPRODUCT((C6:C73="U-19")*1,(J6:K73="Elton S")*1) but it won't allow me to do that. i get a #value error. Is it because i am trying to do 2 columns??? The J6:K73 is the error. can you help?? "Per Jessen" wrote: On 26 Maj, 19:31, Airchief wrote: Help Open this screen wide... I know I can do this with aray. Can anyone help me?? 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 Hi This should do it =SUMPRODUCT((C3:C73="U-19")*1,(G3:G73="Elton")*1) Regards, Per- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Which column in an array of columns | Excel Worksheet Functions | |||
| How do I name rows and columns in an array | New Users to Excel | |||
| Please help -Columns Array in SubTotals | Excel Programming | |||
| Columns Selected Array | Excel Programming | |||
| build array of columns | Excel Programming | |||
![Airchief[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
