 |
 | 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 / Worksheet Functions / February 2007moving the refrence to the 5th cell while dragging.
Hi All, The formula in cell C32 id =COUNTIF(C14:G14,"P"). This is just the attendance count for one week. Now in the next column I want next week's attendance count and when I drag it, the reference moves just one cell ahead...it becomes =COUNTIF(D14:H14,"P"). I want the refrence to move 5 cells ahead so when I drag it to the next column, it should be =COUNTIF(H14:L14,"P") and I have 26 weeks. Please help. Thanks Gary In C32: =COUNTIF(OFFSET($C$14:$G$14,0,(COLUMN()-COLUMN($C$32))*5),"P") HTH Kostis Vezerides > Hi All, > [quoted text clipped - 8 lines] > Thanks > Gary Try something like this: C32: =COUNTIF(INDEX(14:14,1,(COLUMNS($C:C)-1)*5+3):INDEX(14:14,1,(COLUMNS($C:C))*5+2),"P") Copy across to the right. Is that something you can work with? *********** Regards, Ron XL2002, WinXP > Hi All, > [quoted text clipped - 8 lines] > Thanks > Gary =SUMPRODUCT(--(OFFSET($A$14,0,(MIN(COLUMN(A1))-1)*5+2,1,5)="P"))  SignatureHTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi All, > [quoted text clipped - 8 lines] > Thanks > Gary =COUNTIF(OFFSET($C14,0,5*(COLUMN()-3)):OFFSET(G$14,0,5*(COLUMN()-3)),"P") SignatureDavid Biddulph > Hi All, > [quoted text clipped - 8 lines] > Thanks > Gary Thanks everyone.....one more problem. I have dates running from 1/1/2007 to 29/6/2007 from C2 to EB2 and I have attendance as either P or L in the row 14 (C14 to EB14). Now I want to count all the Ps in a month. I want the formula to look for month 1 in row 2 and then count Ps in row 14. Also I would need to add the count of both Ls and Ps for some other purpose. Thanks Gary > Hi All, > [quoted text clipped - 8 lines] > Thanks > Gary P: =SUMPRODUCT(--(MONTH(C2:EB2)=1),--(C14:EB14="P")) P&L: =SUMPRODUCT(--(MONTH(C2:EB2)=1),--(ISNUMBER(MATCH(C14:EB14,{"P","L"},0)))) month tested is Jan here. SignatureHTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) > Thanks everyone.....one more problem. I have dates running from 1/1/2007 to > 29/6/2007 from C2 to EB2 and I have attendance as either P or L in the row [quoted text clipped - 18 lines] > > Thanks > > Gary Thanks everyone.....one more problem. I have dates running from 1/1/2007 to 29/6/2007 from C2 to EB2 and I have attendance as either P or L in the row 14 (C14 to EB14). Now I want to count all the Ps in a month. I want the formula to look for month 1 in row 2 and then count Ps in row 14. Also I would need to add the count of both Ls and Ps for some other purpose. Thanks Gary > Hi All, > [quoted text clipped - 8 lines] > Thanks > Gary |
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
|
©2009 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.