| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
moving 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
moving the refrence to the 5th cell while dragging.
In C32:
=COUNTIF(OFFSET($C$14:$G$14,0,(COLUMN()-COLUMN($C$32))*5),"P") HTH Kostis Vezerides On Feb 8, 6:35 pm, "Gary" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
moving the refrence to the 5th cell while dragging.
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 "Gary" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
moving the refrence to the 5th cell while dragging.
=SUMPRODUCT(--(OFFSET($A$14,0,(MIN(COLUMN(A1))-1)*5+2,1,5)="P"))
-- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gary" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
moving the refrence to the 5th cell while dragging.
=COUNTIF(OFFSET($C14,0,5*(COLUMN()-3)):OFFSET(G$14,0,5*(COLUMN()-3)),"P")
-- David Biddulph "Gary" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
moving the refrence to the 5th cell while dragging.
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 "Gary" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
moving the refrence to the 5th cell while dragging.
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 "Gary" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
moving the refrence to the 5th cell while dragging.
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. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gary" wrote in message ... 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 "Gary" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Don't change cell content in Excel by dragging | Excel Worksheet Functions | |||
| Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
| Slow Moving From Cell to Cell | Excel Discussion (Misc queries) | |||
| Moving from cell to cell | Excel Discussion (Misc queries) | |||
| AutoFIll -by dragging the bottom-right corner of the cell | Excel Discussion (Misc queries) | |||
Linear Mode
