Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSUM criteria problem (97 SR-2)

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

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Attached Files Attached Files

  4. #4
    New Lounger
    Join Date
    Feb 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    New Lounger
    Join Date
    Feb 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •