1. ## 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?)

2. ## 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. ## 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. ## 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.

5. ## 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.

6. ## 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. ## Re: sumproduct() annoyance

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

8. ## 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.

9. ## 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. ## Re: sumproduct() annoyance

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

11. ## 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.

12. ## 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. ## 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.

14. ## Re: sumproduct() annoyance

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

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

15. ## Re: sumproduct() annoyance

It's not mathematically the same. Thanks,

Page 1 of 2 12 Last

#### Posting Permissions

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