Countif & Vlookup

[mickiey]

Hi there
can any please help with this request;

i have a date say in "A1" which is gotten from a Vlookup from say dates in column"C", this works fine. however i am trying to use the date in "A1" to count the amount of dates in say column "E" which are before the date in "A1" but i keep getting 0 which i know is incorrect, if i reference the date which is not from the vlookup but manually enterd into another cell then it works, any suggestions.

mickiey

 

[ps1]

Try this:

=COUNTIF(E:E,"<"&A1)

 

[Aladin Akyurek]

Care to post both the look up and count formulas?

 

[mickiey]

Hi there, thanks for that but i still have a zero instead of the correct number, is it because the date come from a vlookup as when i use a date enterd directly it works?

regards

 

[mickiey]

=VLOOKUP(B1,Dates!A:F,2,FALSE) this is my lookup which gives me a date in "N3"
=COUNTIF(C2:C65536,"<"&N3) i then use this to find how many dates are before the date in "N3"

hope this makes sense?

 

[Aladin Akyurek]

=VLOOKUP(B1,Dates!A:F,2,FALSE) this is my lookup which gives me a date in "N3"
=COUNTIF(C2:C65536,"<"&N3) i then use this to find how many dates are before the date in "N3"

hope this makes sense?

The set up is correct. Logically speaking, one has to conclude that there no dates in the target range smaller than that in N3.

What do you get with each of the following:

=ISNUMBER(N3)

=TEXT(N3,"dd-mmm-yy")

=SUMPRODUCT(--(C2:C65536 < N3)+0)

meant as diagnostic tests?

 

[mickiey]

Hi there i must have been really tired last night, i have just checked an di had the wrong cell referenced it now works.
however i now have another issue which is this, i want to do a countif between two dates in two cells based on my previous vlookup. unfortunatly i cant get this to work any suggestions this is what i have tried,

=COUNTIF(C:C,"<"&L3,">"&L4)

 

[Aladin Akyurek]

Hi there i must have been really tired last night, i have just checked an di had the wrong cell referenced it now works.

Good grace. I missed that possibility!...

however i now have another issue which is this, i want to do a countif between two dates in two cells based on my previous vlookup. unfortunatly i cant get this to work any suggestions this is what i have tried,

=COUNTIF(C:C,"<"&L3,">"&L4)

=COUNTIFS(C:C,">"&L4,C:C,"<"&L3)

if you are on a post-2003 system.

Otherwise:

=COUNTIF(C:C,">"&L4)-COUNTIF(C:C,">="&L3)

 

[mickiey]

Hi there, this works really well the only issue is that it produces a negative number i.e -124 when the answer is 124, any ideas

 

[Aladin Akyurek]

Good grace. I missed that possibility!...



=COUNTIFS(C:C,">"&L4,C:C,"<"&L3)

if you are on a post-2003 system.

Otherwise:

=COUNTIF(C:C,">"&L4)-COUNTIF(C:C,">="&L3)

Hi there, this works really well the only issue is that it produces a negative number i.e -124 when the answer is 124, any ideas

Is L3 > L4 or L4 > L3?