Dsum Function

[sandieg]

I have a spreadsheet that uses DSUM but the criteria (which encompases two cells ie: D1 & D2 finds all simialar items ie: X0213M & X0213MP & X0213MPP when I only want to specify the first criteria.

The function reads (=DSUM('M310+350'!$A2:$S6000,'M310+350'!$R2,C2:C3) The Criteria in C2 refers to the column of the array, C3 refers to the specific criteria. Is there an exact function that can give me the exact cells?

Thank you.

 

[DaleWatson123321]

sandieg,

I've quickly developed an example model for you, based on your description. If you like, I can email it to you. You might find it easier to understand what I've done.

Here are the steps, however, for the benefit of anyone else who might want an explanation...

1) Create a separate sheet for the various criteria for your database formulas, data-filtering, etc. (name it something like "Criteria". Having the criteria on a SEPARATE sheet is HIGHLY recommended because it will eliminate problems that Excel can cause if you have the criteria on the same sheet as your database list.

2) On the Criteria sheet, enter the following formula into cell B3 (for example... you can place it wherever you prefer).

=Fld_1=item_1

3) "Fld_1" is the name I chose for a "field name". Replace "Fld_1" with whatever field name you have used for the column in which your record identifiers exist - i.e. X0213M & X0213MP & X0213MPP.

4) The cell above the formula (cell B2) needs to be BLANK.

5) Create a range name (example "crit&quot for these two cells: B2:B3)
Method:
a) Highlight the range
b) Hold down the <Ctrl> key and hit <F3>
c) Type the name
d) Hit <Enter>

6) Enter the label &quot;crit&quot; in A2. This will serve as an identifier for the range name you've created in B2:B3. You should do the same for other range names you assign for criteria.

7) For this example, enter &quot;X0213MP&quot; in cell B6.

8) Create the range name &quot;item_1&quot; for cell B6, and enter the label &quot;item_1&quot; in cell A6.

9) Create the range name &quot;data&quot; for your database range. The first row of your range needs to include all your field names, and cover all your data. If you prefer, the range can extend beyond the current range occupied by your data - i.e. you can leave room the largest number of records you'll be entering.

10) On your separate summary sheet (wherever you require the DSUM formula), enter this formula...
=DSUM(data,&quot;Fld_2&quot;,crit)

- where you replace &quot;Fld_2&quot; with whatever field name you are using for the column containing the numbers you want to sum.

&quot;Guaranteed&quot; to work. But if you encounter any difficulty, don't hesitate to ask for the file.

Hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[SkipVought]

I would have bet a million CANADIAN DOLLARS, that Dale Watson would be answering this post!



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sandieg]

That did work great.. thanks! This paticular database is very large and setting up the filter as described was time consuming. I may look into SUMIF for this particular application. I am happy to learn more about filtering and hope to put it in use on future spreadsheets.

Sandie