# 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

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.

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

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)

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1