"Help with DSUM on Excel" Topic

Caveat – If you don't know what the DSUM fx does/works, you are unlikely to be able to help.

A spread sheet at work has the name of each client, their supervisor, and the level of customer. The way it does this is by having four columns (one for each level) and we put a 1 in the appropriate column for the client's line.

I put together a table show what allocation each supervisor has using DSUM. The problem is that you have to give DSUM a range of things to look for, the range being inclusive of the column title. This means past the first line you have to count the entries for everyone above the person you are looking at. Ive got round this by then subtracting the total of all the cells above.

Is there a more elegant way – one where it only references the person on the line you are calculating

The data base looks like

Client    Super    Class W    X    Y    Z

With a '1' being entered in Class A, B, C, D (no I can't convince them to change this!)

The table is

Super    W    X    Y    Z
John
Dave
Nick
etc

Assume it starts in cell A1

So I have to do for all below John
DSUM(Dbase location, Ref to name column – Nick is A4, and I have to include the Column header, Column shifts – Type A is col 2, B is Col 3 etc)
DSUM(Data!C:G,A1:A4,2)
Gives the total for Class W whose supervisors are John Dave or Nick.

Is there a way to total Just Nick?

Last Hussar:

With a '1' being entered in Class A, B, C, D (no I can't convince them to change this!)

That's too bad, as it would be much easier if all level "codes" in one column. I can't help with the DSUM, as I've never used it, but whenever I need help like this, I've recieved great fast responses from excelforum.com.

Can't you just do this with the sumif function?

Thanks pphalen – Changes made. It isn't the programming, it's knowing what you are looking for: I sit there going 'It must be able to do this but what is it called…"