DSUM Problems

[www.ttdown.com]

I have a spreadhseet with the following data:

A1 = Procedure Code (which is a column name)
B1 = Minutes (column name)
A2 = H2017
A3 = H2017TJ
A4 = H2017
A5 = T1023
B2 = 15
B3 = 30
B4 = 60
B5 = 60

D1 = Minutes
D2 = H2017
D3 = the formula =DSUM(A1:B100, "Minutes", D12)

E1 = Minutes
E2 = H2017TJ
E3 = the formula =DSUM(A1:B100, "Minutes", E1:E2)

F1 = Minutes
F2 = T1023
F3 = the formula =DSUM(A1:B100, "Minutes", F1:F2)

The range A1:B5 contains data that make up a database of procedures
codes (Column A) and the amounts of time that staff spend doing each
code (Column B). D1:F3 contain my criteria table and formulas to
total the amount of time spent in each code.

The formula =DSUM(A1:B100, "Minutes", D12) etc. works well when the
Procedure Codes are disimiliar (H2017 compared to T1023) but they
total all minutes in each code when the codes are similiar (H2017
compared to H2017TJ).

Does anyone know of a way to work around this problem without changing
the names of the codes? Excel doesn't seem to be able to distinguish
between H2017 and H2017TH when using DSUM.

Thanks!!!

 

[Ron Rosenfeld]

I have a spreadhseet with the following data:

A1 = Procedure Code (which is a column name)
B1 = Minutes (column name)
A2 = H2017
A3 = H2017TJ
A4 = H2017
A5 = T1023
B2 = 15
B3 = 30
B4 = 60
B5 = 60

D1 = Minutes
D2 = H2017
D3 = the formula =DSUM(A1:B100, "Minutes", D12)

E1 = Minutes
E2 = H2017TJ
E3 = the formula =DSUM(A1:B100, "Minutes", E1:E2)

F1 = Minutes
F2 = T1023
F3 = the formula =DSUM(A1:B100, "Minutes", F1:F2)

The range A1:B5 contains data that make up a database of procedures
codes (Column A) and the amounts of time that staff spend doing each
code (Column B). D1:F3 contain my criteria table and formulas to
total the amount of time spent in each code.

The formula =DSUM(A1:B100, "Minutes", D12) etc. works well when the
Procedure Codes are disimiliar (H2017 compared to T1023) but they
total all minutes in each code when the codes are similiar (H2017
compared to H2017TJ).

Does anyone know of a way to work around this problem without changing
the names of the codes? Excel doesn't seem to be able to distinguish
between H2017 and H2017TH when using DSUM.

Thanks!!!

First of all, in D1, E1, and F1 I believe you should have "Procedure Code"
rather than "Minutes". But that's probably a typo or your formula wouldn't be
working.

For your problem, from HELP for Excel: "All items that begin with that text
are filtered. For example, if you type the text Dav as a criterion, Microsoft
Excel finds "Davolio," "David," and "Davis." To match only the specified text,
type the following formula, where text is the text you want to find. =''=text''

So, all you need to do is replace for example in D2 where you have
H2017
replace it with

="=H2017"

and life should be good


--ron

 

[Dave Hawley]

Hi

In addition to the reply, go here
http://www.ozgrid.com/download/default.htm

and download "DFunctionsWithValidation.zip" for some ways to use the
Database functions.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***