Countif & Vlookup

mickiey

Board Regular
Joined
Jan 9, 2013
Messages
55
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

mickiey

Board Regular
Joined
Jan 9, 2013
Messages
55
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

Board Regular
Joined
Jan 9, 2013
Messages
55

ADVERTISEMENT

=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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
=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

Board Regular
Joined
Jan 9, 2013
Messages
55

ADVERTISEMENT

Hi there i must have been really tired last night, i have just checked an di had the wrong cell referenced:confused: 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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Hi there i must have been really tired last night, i have just checked an di had the wrong cell referenced:confused: 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

Board Regular
Joined
Jan 9, 2013
Messages
55
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

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