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

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

Does that help?

Stev

 

[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


+-------------------------------------------------------------------+
|Filename: SumProduct Table.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4458 |
+-------------------------------------------------------------------+

 

[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


4463


+-------------------------------------------------------------------+
|Filename: SumProduct Table.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4463 |
+-------------------------------------------------------------------+