# Countif & Vlookup

#### mickiey

##### Board Regular
Hi there

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

### 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.
Try this:
Code:
``=COUNTIF(E:E,"<"&A1)``

Care to post both the look up and count formulas?

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

=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?

=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?

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)

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)

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

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?

Replies
13
Views
384
Replies
11
Views
338
Replies
3
Views
201
Replies
7
Views
129
Replies
7
Views
253

1,217,677
Messages
6,137,943
Members
450,100
Latest member
mscetr

### 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.

### Which adblocker are you using?

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

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