Excel DSUM Help

[spiderusa]

Hello,

I have a DSUM formula in my spreadsheet that looks like this - =DSUM(Sheet1!$A:$L, Sheet3!$CK7, Sheet3!$F1:$O2). F1 to O2 in Sheet 3 is a set of fields with corresponding values that the function tests against.

Name	Name	Status	Ops	Grade	Score	Amount	Date	Sum	Description
	 <>*AL*	 FAC				                BUDGET	            1/1/2012

Now my issue is I need to exclude certain names from the sum. I worked around by including another column in G1 that excludes all names with 'AL' in them. This is not scalable as I'm forced to create a column for each combination that needs to be excluded.

How do I exclude values in the Dsum function?

Thanks.

 

[SkipVought]

hi,
[tt]
="<>*AL*"
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[spiderusa]

Hi Skip,

The code I have in works. It is just that when there are other names to be excluded like '*AB*' or '*80*' or '*()*', I have to create a column for each. There could be 50 different exlusions and I do not want to create or maintain 50 columns. I'm looking for other options within Dsum to exclude these values.

 

[SkipVought]

That seems to be the way that DSUM() works.

Otherwise, you could write a Function that could use an exclusion list, but it might get hairy.

Another thought.
[tt]
1. Make a list like
="<>*AB*"
="<>*80*"
="<>*()*"

2. COPY the list

3. select the cell in the criteria range and Edit > Paste Special --TRANSPOSE
[/tt]
You might even be able to automate adding this list as outlined, for your Name criteria.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[spiderusa]

Would it be possible to add all exclusions in a single cell like = "<>*80" or "<>*()* and so on?

 

[SkipVought]

how would that be any better procedurally?

secondarily, it is not logically an OR that you want. It is an AND.

In a criteria matrix, ORs are listed vertically while ANDs are listed horizontally.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[spiderusa]

It would be better as I have a more than one Dsum criteria range. Rather than use 50 columns in each range, I can input all exclusions in a single cell. The AND makes sense. Any idea how to code it?
Thanks.

 

[SkipVought]

One cell does not work AFAIK.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}