DSum function

[Robert]

I am using the DSum function in a spread sheet to extract
partial information from a data list I created according
to the criteria set forth by me in the DSum formula. The
criteria includes more than one column from the data one
being the column labeled "Option". In my data this column
will have one of the following (A,B,C or C1). The problem
is that when the criteria for "Option" is C the DSum
function cannot differentiate between those lines with C
from those with C1 and adds them all together. However
when the criteria for "Option" is C1 it sums only those
lines with C1 in the "Option" column which is what it
should do. I changed C1 to C 1 to CC with no change in
results when the criteria is C. This appears to be a flaw
in the programming of the DSum function. It would appear
the programming is not looking for an exact match but an
approximate match. Only when C1 was changed to D in my
data list did I get the correct results.

Thanks for any help or comments
Tried to e-mail Microsoft about this but when I click were
it says to go to inform them of product bugs there was no
selection to go the next step

Robert

 

[Peo Sjoblom]

Works fine for me, assume the table is called "Database", the column header
with the values you want to sum is called
"Total". For instance if we put the criteria in H2 then in H2 put this

=B6="C"

where B6 is the first cell with data in the column with the A, B, C and C1

leave H1 empty and use the below formula

=DSUM(Database,"Total",H1:H2)

if you don't use defined names or headers it might look like this

=DSUM(B5:C200,2,H1:H2)

where B5:C200 is the table, 2 is the index number of the columns (if the
numbers you want to sum is in the second column) and H1:H2 the criteria

you could also use sumproduct for this


=SUMPRODUCT(--(B6:B200="C"),--(C6:C200))

 

[Robert]

Maybe my workbook has become corrupted some how. I have
used the dsum function many times before and do not recall
running into this problem. I often use range names for
this application. I often set them up in this manner (
don't know if you will see what I type, quite often when
thing are lined up when typed they are mis-aligned when
sent to someone)
A B C
1 C1
2 Acct Option
3 49 C1
4 Acct Option
5 50 C1
6 Acct Option
7 51 C1

Cells B3,B5,B7,etc... are referenced to cell B1. By
changing cell B1 from A to B to C to C1 I get the results
for each of these options. But as I said when I type in C
into B1 it adds C & C1 together. Criteria ranges are
A2:B3,A4:B5,A6:B7,etc...

Thanks for your response.