Data table - wrong sums (DSUM)

[OysteinS]

Example: let's say I'm making a data table (DSUM) to display order income pr sales rep. I have one sales rep called Peter, the other Peter Pan. I'm making a data table with the years 2010 and 2011 horizontally and the names Peter and Peter Pan vertically.

The problem is that Excel sums up all data for Peter Pan and displays nothing for Peter. It seems like it summing up alpha numeric.

What am I doing wrong?

 

[RoryA]

How are you actually using DSUM?

 

[OysteinS]

=DSUM(Data!$A$4:$O$1048576;7;H3:K4)

where
Data!$A$4:$O$1048576 is my data range
7 is the column of data that sould be summed
H3:K4 holds my cirteria year and sales rep

 

[RoryA]

Is that for Peter or for Peter Pan? Or are you only summing one at a time?
Also, what precisely is in h3:K4?

 

[OysteinS]

H3 holds a label cell = Year
K3 holds a label cell = Rep
H4 is blank, but could have been used to limit data range to e.g 2010
K4 is blank, but could have been used to limit data range to e.g Peter

 

[OysteinS]

The data contains (among others) the key columns:

Year
Rep
Total (amount to sum up = column 7)

 

[RoryA]

If they are both blank you will get all data.
I'm not really clear on what your layout is, but by default if you enter Peter you will get data for anything beginning with Peter so you would get both Peter and Peter Pan, but for Peter Pan you should only get Peter Pan. For just Peter, enter ="=Peter"
This sounds like the opposite of what you said though?

 

[OysteinS]

Well, I leave K3 blank beacuse I want to display data for all reps.
Remaning the reps like Peter_L and Peter_Pan might solve the problem

 

[RoryA]

Change K3 to something like:
="="&K5

and then set the input cell for the Rep to be K5 rather than K3, and the formulas should work.

 

[OysteinS]

I'll try that, but for now I solved it by renaming the reps...