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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=521899