Not calculating dynamic range with blanks

[Ochimus]

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

[CK76]

You should base your PRIME range as per Enquiries column.

Something like...

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

This will return 28.

See attached.

[JohnTopley]

In Name Manger for "PRIME"

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$1000,LOOKUP("zzzzzz",Sheet1!$A$2:$A$1000,ROW(2:1000)),1)

[Ochimus]

Many thanks to both for coming up with the solutions and pointers.

Should have thought for myself to use the Col B range to set the last row.

Can now mark this as "solved"

Thanks again

Ochimus