Adding an OFFSET condition to a COUNTIF??

  • Thread starter Thread starter Simon Lloyd
  • Start date Start date
S

Simon Lloyd

Hi al,
Is it possible to write a formula like the one below? of course the one
below doesnt work, but i need the formula to count the value in the cell
(in this example its E2) but only if the date lies between certain dates
in column A, so it would count in multiples of eight each instance of a
text found between the first 2 dates and in multiples of 12 in the 2nd
two dates etc.

=COUNTIF(Hols,E2 OFFSET(0,5) >=1/1/06 or <=17/4/06)*8+COUNTIF(Hols,E2
OFFSET(0,5) >=18/4/06 or <=8/10/06)*12+COUNTIF(Hols,E2 OFFSET(0,5)
=9/10/06 or <=31/12/06)*8+F2
Click to expand...

Anyone know how?

Hope you can help

Simon
 
Don't understand where E2 and the OFFSET come into it.

It is easy to count how many Hols come into the specified dates, and
multiply by 8, but the rest?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Simon Lloyd" <[email protected]>
wrote in message
 
Thanks again for the reply Bob,

This is driving me nuts!, the COUNTIF statement without all the OFFSET
rubbish works fine for counting each instance of a text (E2 was just
the formula in that particular cell i copied the formula on but changes
in each cell as it is looking for a different name) and multiplying it
by either 8 or 12 to accumilate the amount of hours booked off. The
trouble is that when another row is added in the range of any of the
hols the range is staying confined to the cells i first defined for it
i.e =Holidays!$A$14:$AK$123 instead of =Holidays!$A$14:$AK$124, it isnt
growing with the new line added, similarly the next range is staying
where it is =Holidays!$A$124:$AK$343 which is causing problems with my
COUNTIF as it doesnt know whether its 8 or 12 hours to count. I can
mail the workbook if you would like.

You can mail me at simon.lloydATkelloggDOTcom

Thanks,

SImon
 
Back
Top