Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    sumproduct() annoyance

    I am unable to get the following formula to work across a workbook with multiple sheets, each representing a US state, where cell C2 is customer population for the state and c5 is another value I need weighted:

    =sumproduct(AL:WY!$C$2,AL:WY!C5)/sum(AL:WY!$C$2)

    nor does an array seem to work just to derive the sumproduct portion:

    ={AL:WY!$C$2*AL:WY!C5}

    Am I doing it wrong or is this just another MS failure to account for an obvious need? (There are two hiddeen sheets in the middle of the bunch, is that relevant?)
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumproduct() annoyance

    I'm not sure what your AL:WY! is.
    I put numbers into Sheet1, cells A2 to A9
    and numbers into Sheet2, cells A2 to A9
    In Sheet3, cell A1, using the PasteFunction button, I put the formula
    =sumproduct(Sheet1!A2:A9,Sheet2!A2:A9)
    which gives the result
    (Sheet1!A1*Sheet2!A1+Sheet1!A2*Sheet2!A2+ ... +Sheet1!A9*Sheet2!A9)

    Is this what you want?
    Ruth

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumproduct() annoyance

    Ruth, IMO John wants to use the sumproduct formula "accross the States" which makes it an array formula (to be entered with CTRL+SHIFT+ENTER). Did not manage to make it work (yet).

    John, can you split it ? Add the sumproducts for each state ?

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: sumproduct() annoyance

    Hi Ruth. Are you a Novocastrian?

    What you have isn't what I'm after. I have a spreadsheet where each sheet is a US State, with population in cell c2, AL = Alabama ... (other states)... WY = Wyoming, hence AL:WY!C2. In cell C5 (and many others) in each state I have a value. On summary sheet "ALL" I want to calculate the average C5 value weighted by state population.

    =SUMPRODUCT(AL:WY!C2,AL:WY!C5)/ALL!$C$2 doesn't work

    It appears I can't attach an example in reply mode.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: sumproduct() annoyance

    I had to split it by State, into the ugly

    =(AL!C5*AL!$C$2+AR!C5*AR!$C$2+AZ!C5*AZ!$C$2+GA!C5* GA!$C$2+IL!C5*IL!$C$2+MI!C5*MI!$C$2+MN!C5*MN!$C$2+ MO!C5*MO!$C$2+NJ!C5*NJ!$C$2+OK!C5*OK!$C$2+OR!C5*OR !$C$2+PA!C5*PA!$C$2+SC!C5*SC!$C$2+TX!C5*TX!$C$2+WA !C5*WA!$C$2+WI!C5*WI!$C$2+WY!C5*WY!$C$2)/ALL!$C$2

    type format.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumproduct() annoyance

    SUMPRODUCT expects arrays for its parameters, yet for some reason, will not recognize a multi-sheet argument. You can use that construction with the SUM function as long as you don't make the SUM an array formula. For example, =SUM(AL:WY!C5) will work but =SUM(AL:WY!C5*AS:WY!B5) will not. (I typed this in as symbolic examples -- they're should probably be some 's in there somewhere..)

    I even tried to define named ranges using both the multi-sheet selection approach and identifing the reference as each sheet reference individually separated by commas -- Neither of which would work.

    My thought would be to build a couple of rows or columns on your summary sheet and link each of the state sheets there. Then do your sumproduct/count calculation on those ranges.

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumproduct() annoyance

    Try =SUM(PRODUCT(AL:WY!C5;AL:WY!$C$2))

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: sumproduct() annoyance

    The result of =SUM(PRODUCT(AL:WY!$C$2:AL:WY!C5)) is #VALUE!

    The result of =SUM(PRODUCT(AL:WY!$C$2,AL:WY!C5)) is not mathematically correct.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumproduct() annoyance

    =SUM(PRODUCT(range,range2)) returns the product of ALL the numbers -- not what I think he wants. I think he wants the Sum of the products of each pair which is quite different.

  10. #10
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumproduct() annoyance

    Sorry, did not check the result _and_ forgot to change the list separator from my ";" to yours ","

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: sumproduct() annoyance

    It sucks that =sumproduct() won't handle a multi-sheet argument. Thanks for verifying that it wasn't my ineptitude that prevented me from doing the function!

    I may move to your recommendation of adding the data for each state on the summary, but I actually have 680 values which I'm trying to weight by state population. One of those "the cure may be worse than the disease" issues.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumproduct() annoyance

    Excel does not allow 3d references in arrays. So as a workaround, I did the follwing:
    I wrote a module to create named formulas for each of the sheets in the workbook that takes the C2/C5 for each sheet.

    I also created a named formula that sums the 3d range of the Population cells from each sheet in the workbook.

    Then it doesn't seem so bad to use these formulas on the summary sheet.

    It appears that the bulk of the work would be to produce each of the individual formulas for the sheets. So the following tid bit of code may save you some time.

    Hope it helps.

    Regards,

    Rich P.

    Sub MakeFormulaNames()
    '

    Dim shtName As String

    For Each sht In ActiveWorkbook.Sheets

    shtName = sht.Name

    ActiveWorkbook.Names.Add Name:=shtName & "_Prod", RefersToR1C1:= _
    "=" & shtName & "!R2C3*" & shtName & "!R5C3"
    Next sht
    End Sub

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: sumproduct() annoyance

    Thanks, Rich. I would have to create the formula 680 times on each worksheet, but that's easily done by editing in multi-sheet mode. It's still an inelegant solution to me; MS should see the need for across-sheet 3D arrays and allow them in Excel.
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    SuperShoe
    Guest

    Re: sumproduct() annoyance

    have you tried =(sum(al:wy!c2)+sum(al:wy!c5))/sum(al:wy!c2
    instead of
    =sumproduct(AL:WY!$C$2,AL:WY!C5)/sum(AL:WY!$C$2)

    [img]/w3timages/icons/yikes.gif[/img]

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: sumproduct() annoyance

    It's not mathematically the same. Thanks,
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 2 12 LastLast

Posting Permissions

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