Array using 2 columns

  • Thread starter Thread starter Airchief
  • Start date Start date
A

Airchief

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
 
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
Click to expand...


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??
 
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))
 
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
 
Per
I got the same #value erros mess. I tried several thing but same erro.
 
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)
 
That wasn't the formula I suggested.

Both the suggestions that I made worked ok for me.
 
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!!!
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Outlook crashes with Outlook connector 2002 0

Back
Top