DSUM criteria problem (97 SR-2)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

I’m happy with your array – it works! Thanks again for your time.

Reply | Quote