Database Formula Criteria Problem

[johndonnelly]

I am using a DSUM formula to total up accounting entries for numerous
accounts. I use a named range for the database (WTB01) and a named range for
the Criteria (UCRS01) and a formula for the SUM column (MONTH(NOW()) +28) as
follows =DSUM(WTB01,MONTH(NOW()+28,UCRS01)

The criteria range values (in UCRS01) are:

ACCT
446
446A
446B
446X
446Z
450
450A
450B
450Z

The formula works fine for all accounts in the range but it is summing an
extra acct it shouldn't named 446BA

It seems to be extending the criteria to all values whose first digits match
instead of requiring all digits to match.

I hope this is clear and thanks for the help.

John Donnelly

 

[Max]

I'm not familiar with db functions, and I'm not sure why it's happening the
way you described in your post.

Perhaps try this equivalent formula which uses your named ranges:
=SUMPRODUCT(--ISNUMBER(FIND(UCRS01,INDEX(WTB01,,1))),INDEX(WTB01,,MONTH(TODAY()+28)))

From my tests here, the above works ok. It won't pick up the extra account.

 

[johndonnelly]

Hi Max,

Thanks. That formula works. In case you want to know I found another post
that answers it.

I like to use criteria ranges because when one of the accountants adds an
account I just add to the list. Anyway if in the criteria range you just put
a string the DSUM formula looks for the presence of that string in all cells
whether or not the cell contents includes other characters. To make the
selection specific the criteria must be preceded by an equal sign in the cell
such as =446B not just 446B. This way it will not select cells containing
characters other than the criteria such as 446BA.

Anyway thanks for your help Max, I appreciate it.

 

[Max]

welcome, and thanks for the explanation, John.
glad to hear you found what you were after.