# Thread: DSUM criteria problem (97 SR-2)

1. ## DSUM criteria problem (97 SR-2)

DSUM example:. DSUM function is used to sum sales for each company from a database that lists all individual sales for each company. Database includes a "Company" column with names: e.g., alpha, beta, alpha-1, and a "Sales" column for sales figures. Field is the "Company" column. Criteria is a series of 2-row columns, e.g., Company / alpha; Company / beta, Company / alpha-1. Problem: DSUM formula that sums up all the sales for "alpha" company also adds in all the sales for "alpha-1". Appears it would add sales from any company whose name begains with "alpha". Ideas? Sample attached.

2. ## Re: DSUM criteria problem (97 SR-2)

You might consider changing all instances of plain alpha to alpha-0

3. ## Re: DSUM criteria problem (97 SR-2)

Try this array: =SUM((B16:B21=+B6)*C16:C21) I modified your file to incorporate this change.

After typing in the cell formula, press Cntrl-Shift and Enter.

Cell B6 references the text "alpha"; the returned value should be 300.

4. ## Re: DSUM criteria problem (97 SR-2)

Your suggestion works - thank you!
I am not familiar with arrays, but I seem to remember hearing that large arrays can be very slow to calculate. Will it matter if the array has two columns but perhaps several hundred rows?

5. ## Re: DSUM criteria problem (97 SR-2)

You may want to experiment using the array functionality vs formula and see which you prefer. I have not run into a calculation issue using 10 columns x 8000 rows.

John

6. ## Re: DSUM criteria problem (97 SR-2)

I'm happy with your array - it works! Thanks again for your time.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•