# some formulas using dates are not working ranges named using counta

1. ## some formulas using dates are not working ranges named using counta

Attached sample file. cells marked with red are not working. cells are named using counta of d column. because in d column there is no blank cell. feel there is error while using dates. Thanks in advance as this forum has solved many queries.duplicate of brs 2015-16.xlsx  Register To Reply

2. ## Re: some formulas using dates are not working ranges named using counta

I believe the problem is because the named ranges you are using reference row 1.
Change the first part of your named ranges to row 2.

Here is your formula for bookdate:
Formula:  `Please Login or Register  to view this content.`

Change Sheet1!\$B\$1 to Sheet1!\$B\$2. Do that with your other named ranges also.  Register To Reply

3. ## Re: some formulas using dates are not working ranges named using counta

And change the formula for your Dynamic Named Ranges to use INDEX rather than INDIRECT. Same effect but INDIRECT is volatile and INDEX isn't.

Formula:  `Please Login or Register  to view this content.`

Regards, TMS  Register To Reply

4. ## Re: some formulas using dates are not working ranges named using counta

Try the following formulas

J7 = "=SUMIFS(E:E,B:B,">="&DATE(2015,4,1),B:B,"<="&DATE(2015,4,30))"
K10 = "=SUMIF(A:A,"<="&J10,deposits)-SUMIF(A:A,"<="&J10,issues)"
L10 = "=SUMIFS(F:F,B:B,">="&DATE(2015,4,1),B:B,"<="&DATE(2015,4,30),D:D,D4)"

If you wanna use your current formulas try renaming the range names as follows

bankdate =OFFSET(Sheet1!\$A\$2,,,COUNTA(Sheet1!\$D:\$D))
for bookdate change \$A\$2 to \$B\$2, for citric \$H\$2 and so on

for range name full use =OFFSET(Sheet1!\$A\$2,,,COUNTA(Sheet1!\$D:\$D),8)  Register To Reply

5. ## Re: some formulas using dates are not working ranges named using counta

As i was preoccupied I could not reply. Very much thanks to all of you. Presently I have changed to row2 and date by marking "" in between.   Register To Reply