Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding an OFFSET condition to a COUNTIF??
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 Anyone know how? Hope you can help Simon -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=499674 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding an OFFSET condition to a COUNTIF??
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" wrote in message ... 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 Anyone know how? Hope you can help Simon -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=499674 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding an OFFSET condition to a COUNTIF??
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 -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=499674 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF - more than one condition | Excel Worksheet Functions | |||
Offset/Countif question | Excel Discussion (Misc queries) | |||
Countif from an offset column | Excel Worksheet Functions | |||
Offset, Dynamic range, Countif | Excel Discussion (Misc queries) | |||
vba help pls - find min based on a condition and return val of an offset cell | Excel Programming |