Sumproduct and dynamic data
in [Functions]
|
Prev: function to download stock prices into Excel with 2007
Next: Calendar Formatting Based Upon Multple Date Ranges
From: jtfalk on 6 Mar 2010 18:17 Good afternoon, I have an Excel 2007 workbook that I am having issues with. I have set up a dynamic graph that allows me to put in a start and finish date and it graphs the values in that time frame. It works great right now. What I want to do is have a cell add up the values between these changing dates for another calculation. So B5 (named StartDate) and B6 (named EndDate), the column with all the dates B (named AllDates) the values I need are in the C column. For the dynamic graph I plotted the data this way =BUILD.xlsx'!ChtBuildPlan with chart build plan the column next to the dates. A B C Day Date Build Mon 1/4/2010 0 Tue 1/5/2010 1 Wed 1/6/2010 3 Thu 1/7/2010 6 Fri 1/8/2010 7 So if I put in 1/5 in the StartDate and 1/7 in the finish date the cell G5 would equal 10. I was looking at something like this - I thought =SUMPRODUCT(--('BUILD.xlsx'!AllDates>=StartDate),--('BUILD.xlsx'!AllDates<=EndDate),'BUILD.xlsx'!ChtBuildPlan) Of course since I am posting this it does not work. Any help would be appreciated.
From: T. Valko on 6 Mar 2010 21:31
>=SUMPRODUCT(--('BUILD.xlsx'!AllDates>=StartDate),--('BUILD.xlsx'!AllDates<=EndDate),'BUILD.xlsx'!ChtBuildPlan) >Of course since I am posting this it does not work. There's nothing wrong with the formula so you'll have to be more specific as to what "does not work" means. -- Biff Microsoft Excel MVP "jtfalk" <jtfalk(a)discussions.microsoft.com> wrote in message news:D188B35B-44DD-4518-8E25-9F4F72DE865A(a)microsoft.com... > Good afternoon, > > I have an Excel 2007 workbook that I am having issues with. I have set up > a > dynamic graph that allows me to put in a start and finish date and it > graphs > the values in that time frame. It works great right now. > > What I want to do is have a cell add up the values between these changing > dates for another calculation. > > So B5 (named StartDate) and B6 (named EndDate), the column with all the > dates B (named AllDates) the values I need are in the C column. For the > dynamic graph I plotted the data this way =BUILD.xlsx'!ChtBuildPlan with > chart build plan the column next to the dates. > > A B C > Day Date Build > Mon 1/4/2010 0 > Tue 1/5/2010 1 > Wed 1/6/2010 3 > Thu 1/7/2010 6 > Fri 1/8/2010 7 > > So if I put in 1/5 in the StartDate and 1/7 in the finish date the cell G5 > would equal 10. > > I was looking at something like this - I thought > > > =SUMPRODUCT(--('BUILD.xlsx'!AllDates>=StartDate),--('BUILD.xlsx'!AllDates<=EndDate),'BUILD.xlsx'!ChtBuildPlan) > > Of course since I am posting this it does not work. Any help would be > appreciated. |