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

On 26 Maj, 19:31, Airchief <Airch...@discussions.microsoft.com> 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

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 <Airch...@discussions.microsoft.com> 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
>

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 <Airch...@discussions.microsoft.com> 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

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 <Airch...@discussions.microsoft.com> 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 <Airch...@discussions.microsoft.com> 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 -

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 <Airch...@discussions.microsoft.com> 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 <Airch...@discussions.microsoft.com> 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 -
>
>

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 <Airch...@discussions.microsoft.com> 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 <Airch...@discussions.microsoft.com> 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,
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 <Airch...@discussions.microsoft.com> 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 <Airch...@discussions.microsoft.com> 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
>

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 <Airch...@discussions.microsoft.com> 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 <Airch...@discussions.microsoft.com> 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

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 <Airch...@discussions.microsoft.com> 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 <Airch...@discussions.microsoft.com> 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
>