Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    question about SUMPRODUCT (2002 SP3)

    Hi,

    I've just tried using SUMPRODUCT for the first time and was wondering how flexible it is. I have a large extract that I pull each month and used SUMPRODUCT to calculate Costs and Days for various divisions. I was wondering if I could add a layer to the divisions to get departments within the divisions.

    eg ColA =Division ColB=Dept ColC=Cost and Col4= Days

    at the moment I have

    Division Cost Days
    BS 450. 45
    NS 880 64
    SS 777 59


    Could I get

    Division Dept Cost Days
    BS A 200. 15
    BS B 250. 30
    NS C 480 34
    NS D 400 30
    SS E 377 29
    SS G 400 30

    Or would I have to settle for

    Dept Cost Days
    A 200. 15
    B 250. 30
    C 480 34
    D 400 30
    E 377 29
    G 400 30



    capri

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: question about SUMPRODUCT (2002 SP3)

    Hi capri,

    SUMPRODUCT is quite flexible, you can add up to 30 arguments. But wouldn't a pivot table be easier? You wouldn't have to create formulas.
    If you'd like to pursue formulas with SUMPRODUCT, please provide some more information about the formulas you're using now, or post a small sample workbook (with dummy data).

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: question about SUMPRODUCT (2002 SP3)

    Hans,
    Each month I pull a data extract which has year to date information (our year run July to June). Sometime people want to see the month to month information and other times they just want year to date.
    Pivot tables are easy, and that is what I do now, however I thought this method would save time each month, if I just copied and posted the sumproduct tables (replacing the previous final row of data with the current final row). It would save re-doing pivot tables each month. It might also save on file size as pivot tables tend to bloat my files. My monthly extracts run from 5,000 to 60,000 rows of data depending on the month.

    Attached is a sample of what I have and what I would like. I just need to know how to construct the formula in the shaded green cells.

    capri
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: question about SUMPRODUCT (2002 SP3)

    You can enter

    =SUMPRODUCT((MONTH($E$2:$E$344)=7)*($C$2:$C$344=$O 23)*($B$2:$B$344=$P23)*$D$2:$D$344)

    in Q23, and

    =SUMPRODUCT((MONTH($E$2:$E$344)=7)*($C$2:$C$344=$O 23)*($B$2:$B$344=$P23)*$F$2:$F$344)

    in R23, then fill down. See attached version.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: question about SUMPRODUCT (2002 SP3)

    Thanks Hans,

    That's exactly what I was looking for. Once I set it up, I'll just copy and paste each month, use find and replace to increase the size of the data extract, and I'll have the numbers I need to use, without having to pivot the data, and hopefully my file sizes won't be as large. I regularly lose my work having too many large files with pivot tables open at once, as my PC struggles to handle the data. I can see a lot of potential with this feature of eliminating lots of data I usually pivot. My IT area has been no help with my problems, so anything I can do to save file size makes my life easier.

    capri

Posting Permissions

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