 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2008Array 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 > Help > Open this screen wide... [quoted text clipped - 18 lines] > 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?? > > Help > > Open this screen wide... [quoted text clipped - 27 lines] > 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)) > I modified it a little and it worked GREAT!. Now I wanted to expanded the > second part to [quoted text clipped - 33 lines] > > Regards, > > Per  SignatureDave 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 > I modified it a little and it worked GREAT!. Now I wanted to expanded the > second part to [quoted text clipped - 35 lines] > > - Vis tekst i anførselstegn - Per I got the same #value erros mess. I tried several thing but same erro. > Hi > [quoted text clipped - 46 lines] > > > > - Vis tekst i anførselstegn - Do you have any errors in those ranges? > Per > I got the same #value erros mess. I tried several thing but same erro. [quoted text clipped - 49 lines] > > > > > > - Vis tekst i anførselstegn - SignatureDave 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) > Do you have any errors in those ranges? > [quoted text clipped - 51 lines] > > > > > > > > - Vis tekst i anførselstegn - That wasn't the formula I suggested. Both the suggestions that I made worked ok for me. > Dave, > I use this formula you gave me and seems to work.. [quoted text clipped - 61 lines] > > > > Dave Peterson SignatureDave 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!!! > That wasn't the formula I suggested. > [quoted text clipped - 65 lines] > > > > > > Dave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.