Forum Discussion
Sumproduct
Hello,
I am attempting to add up indexed columns between certain dates. I have had some success but only up to a two week time span. =SUMPRODUCT(--('Upcoming Needs'!$D$3:INDEX('Upcoming Needs'!$3:$3,COLUMNS('Upcoming Needs'!$3:$3))>='Order Indicator'!$A$1),--('Upcoming Needs'!$D$3:INDEX('Upcoming Needs'!$3:$3,COLUMNS('Upcoming Needs'!$3:$3))<='Order Indicator'!$D$3),'Upcoming Needs'!D5:INDEX('Upcoming Needs'!5:5,COLUMNS('Upcoming Needs'!5:5))). This formula gives me accurate results over or between a 14 day time span. Once I up the time span to 21 or more days the result always comes back zero. Think I may be missing something and looking for help,
Thanks
2 Replies
Hello,
Without seeing your data it is hard to troubleshoot. Can you explain in words what you want to achieve? The use of Index with the Columns function will result in a really large range, i.e. from cell C3 to the last column in row 3. Why do you bother with Columns at all, then? If you feed it the whole row, it will always return 16384, so the formula will be evaluated as
=SUMPRODUCT(--('Upcoming Needs'!$D$3:$XFD$3>='Order Indicator'!$A$1),--('Upcoming Needs'!$D$3:$XFD$3<='Order Indicator'!$D$3),'Upcoming Needs'!D5:$XFD$5)
Please post a data sample and explain in context.
Thanks for your thoughts but I managed to get it figured out. To clarify a bit, I use the index/Column because can at times have as many as 200 entries that I am looking or sorting through. These entries will also date as much as 12 months into the future as well. I issue ended up being the use of dates instead of excel serial number for the date, or datevalue. Once I changed to this format, all started to add up correctly.
Again thanks for the help.
