Results 1 to 15 of 20
Thread: sumproduct() annoyance

20010115, 19:15 #1
 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

20010116, 04:54 #2
 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

20010116, 06:05 #3
 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 ?

20010116, 16:07 #4
 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

20010116, 16:09 #5
 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

20010116, 16:52 #6
 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 multisheet 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 multisheet 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.

20010116, 16:52 #7
 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))

20010116, 16:59 #8
 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

20010116, 17:02 #9
 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.

20010116, 17:10 #10
 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 ","

20010116, 17:26 #11
 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 multisheet 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

20010117, 02:43 #12
 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

20010117, 15:09 #13
 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 multisheet mode. It's still an inelegant solution to me; MS should see the need for acrosssheet 3D arrays and allow them in Excel.
John ... I float in liquid gardens
UTC 7ąDS

20010117, 20:47 #14SuperShoeGuest
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]

20010117, 20:49 #15
 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