COUNTIFS with dates smaller than a date in other cell

[virtuosok]

Hi,
Appreciate your help with the following formula which is not working as I would want:

=COUNTIFS('Source'!$E$13:$E$15000, C2, 'Source'!$M$13:$M$15000, "<Z2")

Essentially, I would need to count only those dates in column M which are smaller than the date noted in cell Z2
I have the following issues:
1. Z2 is formatted as Date, format 01 Jan 2000. This cell in itself is an INDEX/MATCH output from exported source though. At the same time, column M on Source tab is formatted as Custom, 01-Jan-2020 (and is also an export from another source). Changing that would be problematic as the inputs are dynamic. I need to somehow compare the values even though the format is different, if at all possible.
2. I'll need the formula which can be pulled down across the whole column, i.e. next cell matching Z3 and so on.

 

[Fluff]

It should be "<"&Z2 not "<Z2" otherwise it's treating Z2 as a value rather than a cell reference

 

[Toadstool]

Hi Virtuosok,

I see Fluff has already advised how to make Z2 relative. I see C2 is already relative so that will increment also as you copy down.

The date formats shouldn't matter as long as they are numeric and only contain dates, not dates and times.

Is this what your sheet looks like? (I put the formula in column G).

Virtuosok.xlsx
	C	D	E	F	G	H					M	N												Z
2	02-Feb-2020																							05 Mar 2020
3	02-Feb-2020																							06 Mar 2020
4	03-Feb-2020																							07 Mar 2020
5	04-Feb-2020																							08 Mar 2020
6	05-Feb-2020																							09 Mar 2020
7	02-Feb-2020																							09 Sep 2020
8
12
13			02-Feb-2020		2						03-Mar-2020
14			02-Feb-2020		2						04-Mar-2020
15			03-Feb-2020		1						05-Mar-2020
16			04-Feb-2020		1						06-Mar-2020
17			05-Feb-2020		0						09-Sep-2020
18			02-Feb-2020		3						04-Apr-2020
Source

Cell Formulas
Range	Formula
G13:G18	G13	=COUNTIFS(Source!$E$13:$E$18,C2, Source!$M$13:$M$18, "<"&Z2)

 

[Fluff]

I see Fluff has already advised how to make Z2 relative.

Actually I haven't, I advised on how to use Z2 as a cell reference & not a string value.

 

[virtuosok]

Thank you both! The revised formula works better, but still doesn't capture everything. Let me bring 2 clips to illustrate. First one is for the actual formula in cell F4:

The other one is the source ("Seizure diary") tab. We have multiple instances of the dates in column M which are before the date in Z4 above, yet formula returns zero.

The entry in Z4 itself is a formula

{=INDEX('Subject summary'!$Q$2:$Q$500,MATCH(S4&T4, 'Subject summary'!$A$2:$A$500&'Subject summary'!$E$2:$E$500,0))}

 

[Fluff]

Check that you don't have any leading/trailing spaces on the ID in C4 & col E, also check that all the dates are real dates & not text.

 

[virtuosok]

Thanks! No concerns about leading/trailing spaces, but the dates in column M may well be a text. Is it possible to embed DATAVALUE into the formula above or otherwise circumvent the limitation?

 

[Fluff]

You would be better of converting the dates to real dates.

 

[virtuosok]

Yeah, if it were not for the dynamic inputs (data imports from third parties couple of times a week and overrides existing source), I would set up helper columns with conversion. I can still try to do that by adding another sheet, but wondering if formulas can do the trick instead.

 

[Fluff]

Try using Text to columns on column M on page 2 of the wizard uncheck all boxes & on page 3 select date & format of DMY.