Countif & offset to find and count data

Capt Geoff

New Member
Joined
Jan 27, 2014
Messages
2
I’m trying to establish a formula to count the number of working days over a date range. <o:p> </o:p>
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:p> </o:p>
Cell F9 is the start date and G9 the end date for the query.
<o:p> </o:p>
I’m trying to use the COUNTIF OFFSET ROW INDEX MATCH functions but can’t get it to work.
<o:p>
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:p>
but it doesn't work. I’d be grateful for some suggestions that will make this work.
<o:p> </o:p>
thanks
Geoff
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

pleeseemailme

Board Regular
Joined
Dec 26, 2013
Messages
201
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

New Member
Joined
Jan 27, 2014
Messages
2
Thank you for the explanation and corrected formula, which does what I need. Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top