Gareth has attended:
Excel VBA Intro Intermediate course
Excel Intermediate course
Excel Advanced course
Using Countif and Offset
Hello
Im having trouble using the countif with offset. i want to count a criteria but I only want to count it from a certain date, once I have sorted the dates in ascending order.
To locate the starting row for the date I have used the match function, no problem with that.
The problem arises when I try using the row reference in the countif and offset function.
Basically I would like to count the number of times the a criteria is met on a a given date (this will always be yesterday and the match function will locate my starting point)
The columns with he dates in ascending order is column C, The criteria I would like to match it up is in column H.
In a nut shell I want the countif and offset function to locate the row that yesterdays date starts, use the offset function to create the array and count the number of times a defined criteria occurs on that day.
Thanks in advance
RE: Using Countif and Offset
Further to the above.
A formula I have worked on (resulting in an error) is this...
=COUNTIF(OFFSET(MATCH($C$2,'Data Extract MTD'!$C:$C,0),0,5,),$B5)
C2 has yesterdays date.
'Data Extract MTD' is the tab with the list of data I want to count. Column C on this tab has the dates in ascending order
Column H is where the criteria is.
Again, basically i want the formula to look for the start position of cell c2, go across 5 columns to column H, then count the number of times a specified criteria is met.
Thanks
