Not calculating dynamic range with blanks

Need to count how many records match a selected text in Col A and were received within a certain period in Col B (e.g. since start of year, or in the previous month).

Col B is a Dynamic range ("Enquiries") which works perfectly.

As Col A has blanks, I have tried three approaches to define it as a Dynamic List ("RANGES"):

Approach 1: "=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$1000,SUMPRODUCT(--(Sheet1!$A$2:$A$1000<>"")))"
Approach 2: "=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,Sheet1!$A:$A))"
Approach 3: "=OFFSET(A2,0,0,COUNTA($A$2:$A$10000),COUNTA($A$2:$A$10000))"

None work.

Any solutions, pointers or alternatives welcome as ever

Ochimus

You should base your PRIME range as per Enquiries column.

Something like...

Formula:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$B:$B),)

Also, to ensure both PRIME & Enquiries has same dimension... Enquiries should be...

Formula:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$B:$B),)

Also, for your COUNTIFS formula, to ensure that it works for any region setting, use Date() function instead of coercing "1Jan" into date.

Formula:

=COUNTIFS(PRIME,"PRIME",Enquiries,">"&DATE(2018,1,1))

This will return 28.

See attached.