Sumproduct

[jcastellano]

I am trying to use the sumproduct equation and I am get a VALUE response.

I am trying to sum up information that is presented in columns F through W, with each column represent a certain weekday. I then have included certain categories in column E (rows 8 through 90) for which I will want to sum into another spreadsheet by category and date. It looks some thing like this:

2/24 3/3 3/10 3/17
Non-Affiliate
Energy Energy 100 250 175 800
Water Energy 300 300 275 900
O&M Other 25 10 20 20

Affiliate
Fuel Fuel 250 250 250 900
O&M Fuel 400 100 250 250
Hydro Other 10 10 15 15

So, I've set up my formula as follows:

=SUMPRODUCT(--('Corporate (2)'!$F$6:$W$90='Final Corp Template-we 24-Feb'!F$6),-('Corporate (2)'!$E$6:$E$90='Final Corp Template-we 24-Feb'!$E13),('Corporate (2)'!F$6:F$90))

Where 'Corporate (2)' is the spreadsheet with the data and 'Final Corp Template-we 24-Feb' is where I would like the results placed.

I don't know if I am having problems as a result of data ranges, but I can't seem to see how to get this to work.

The desired result would be as follows for 2/24:

Non-Affiliate Energy - 400
Non-Affiliate Other - 25
Affiliate Fuel - 650
Affiliate Other - 10

Any help will be appreciated.

[SteveG]

A couple of issues I think. SUMPRODUCT relies on arrays of data that are the same number of cells so A1:A50, B1:B50 etc... Because your date only appears once in the header, SUMPRODUCT cannot use that as a criteria unless you add an array in column D that will include the date. I think from your post that you have data that has a label of Affiliate or Non-Affiliate in column E. Column F has either Energy Energy, Water energy or O&M Other and then in G-? you have amounts for dates listed in the first row of the data set. What I did to make it work was as suggested, I put the dates in column D and used this formula in the "Final Corp Template-we 24 Feb" sheet to pull in the correct number for "Non-Affiliate Energy".

=SUMPRODUCT(--('Corporate (2)'!E2:E4="Non-Affiliate"),--(ISNUMBER(SEARCH("Energy",'Corporate (2)'!F2:F4))),--('Corporate (2)'!D2:D4=DATE(2006,2,24)),('Corporate (2)'!G2:G4))

Does that help?

Steve

[jcastellano]

I am not sure this will work. I have attached a zip file of what I am trying to do and I thought I could get there with a sumproduct formula. If I can't, would you have any suggestions?


John

[SteveG]

John,

Although this may not be the most efficent way of approaching this, I think that the attached excel file works for you but you'll have to check it out. It is dependent upon the fact that your raw data where you are extracting from remains consistent meaning it will always show each weeks data in order as you have it in your example. The CHOOSE function acts on an index number that the MATCH function creates by returning the relative position of the date in the header of the data you are pulling from so in this case, Feb-24 is the first position. The CHOOSE function then applies the correct SUMPRODUCT function based on that. Each SUMPRODUCT function is assigned an index starting with 1 in ascending order each separated by a comma.

Just a suggestion, if you need to post an attachment, do so in excel not PDF. I had to convert this to excel to try and provide you a solution.

HTH

Steve


SumProduct Table.zip