Countif & offset to find and count data

[Capt Geoff]

I’m trying to establish a formula to count the number of working days over a date range. <o> </o>
Column A contains a chronological list of dates, column B the weekday reference and column C identifies if the date is a working day. The pattern of working days may change, hence the need to effectively lookup the query date range.
<o> </o>
Cell F9 is the start date and G9 the end date for the query.
<o> </o>
I’m trying to use the COUNTIF OFFSET ROW INDEX MATCH functions but can’t get it to work.
<o>
the formula I've produced is

COUNTIF(OFFSET(ROW(INDEX($A$9:$A$382,MATCH($F$9,$A$9:$A$382,false),1)),0,2):OFFSET(ROW(INDEX($A$9:$A$382,MATCH($g$9,$A$9:$A$382,false),1)),0,2,"working day"))

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

</o>
but it doesn't work. I’d be grateful for some suggestions that will make this work.
<o> </o>
thanks
Geoff

 

[pleeseemailme]

The reason this is not working is because the first argument in the OFFSET formulas are returning a number, not a cell reference. It looks like you want to use the 'height' and 'width' arguments of the offset function. The way these work is they create a range the number of cells down, up, right or left of the cell you are offsetting. For example: OFFSET($A$1,1,1,2,2) will offset 1 row and one column, and then return the the range two cells wide and high from that position: B2:C3. Does that make sense? I'd play around with that to see if you can get it to work for you.

I think this might be what you are after from the formula above:
=COUNTIF((OFFSET((INDEX($A$9:$A$382,MATCH($F$9,$A$9:$A$382,0),1)),0,2):OFFSET((INDEX($A$9:$A$382,MATCH($G$9,$A$9:$A$382,0))),0,2)),"working day")

 

[Capt Geoff]

Thank you for the explanation and corrected formula, which does what I need. Thanks.