moving the refrence to the 5th cell while dragging.

G

Gary

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
 
V

vezerid

In C32:

=COUNTIF(OFFSET($C$14:$G$14,0,(COLUMN()-COLUMN($C$32))*5),"P")

HTH
Kostis Vezerides
 
R

Ron Coderre

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
 
B

Bob Phillips

=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)
 
D

David Biddulph

=COUNTIF(OFFSET($C14,0,5*(COLUMN()-3)):OFFSET(G$14,0,5*(COLUMN()-3)),"P")
 
G

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
 
G

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
 
B

Bob Phillips

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)
 

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

Top