countif criteria issue

    Hi, I have a spreadsheet I developed with a few macros for our local Sheep Dog Trial club. I’m now wanting to elevate it to a more user friendly application so I can offer it to other clubs.

    I have a section where I’m unsure how to fix and wonder if you could suggest an approach ?

    I have a cell with a date e.g. 27/1/2012 and another cell with time 6:00:00 a.m. I want to combine these two cell references into a combined cell, this combined cell is then used in a countif function as the "criteria" i.e. <27/01/2012 6:00:00 a.m.
    I already have a custom number field with this working BUT I want to write a macro that automates the user process of updating of the date cell annually and the criteria cells of which I have 8 each with a differing time block like the example above. So I'm wanting to try to reference the date part rather than manually edit each of these 8 criteria cells as I do now. I hope this makes sense. Apologises if not clear.

    Thanx

    Re: countif criteria issue


    Don't know about the macro part but in COUNTIF you could simply add those two values as the criteria, e.g. if your data range is column A - your date (for the criteria) is in B2 and time in C2 you can use


    =COUNTIF(A:A,"<"&B2+C2)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login