Excel DSUM - using AND/OR as part of the criteria

Question

I checked all the Dsum questions and they did not answer mine.

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

Name    Status  Ops Grade   Score   Amount  Date    Sum Description
         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 B1 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.

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

Is it possible to include OR/AND in the Name values? e.g. =<>*80*or <>AB* or <>()* etc?

Thanks.

Answer 1

You don't have to include the criteria in the same table as the data. You can use another table for the criteria.

• For each criterion for the same field add a new row in the criteria table (like above).

• If you need an OR put criteria on different rows. If you need and AND put criteria on the same row. For multiple criteria on the same field, you need to add multiple columns.

Name
<>*80*
<>AB*
<>()*

More info here.