# SUMPRODUCT with Date

September 29, 2010 at 07:38:17
Specs: Windows XP
 Hi I am using the following formula to lookup a certain data against a certain date"SUMPRODUCT((Master!B2:B48577=DATE(2010,6,17))*(Master!J2:J48577=S2))"But I want to be able to drag the formula so that I do not have to enter it in for the 55 different dates I have. Is there any other way I can enter the formula which will let me do that?I basically want to the date to point to a cell where I have the date entered.Also is there anyway in copying the formula to the adjacent cell in such a way only the pointing reference changes in the case of my formula"S2" instead of the reference changing to column C instead of B "Master!B2:B48577"Hope I was clear.

See More: SUMPRODUCT with Date

#1
September 29, 2010 at 08:13:53
 Second question first:Look up "relative" and "absolute" references in the Excel Help files.Absolute references don't change when you drag a formula, Relative references do. Your references are all Relative. To change an reference to Absolute, put a \$ in front of the Column letter, the Row number, or both. References with \$ in front of them don't change:=SUMPRODUCT((Master!\$B\$2:\$B\$48577=DATE(2010,6,17))*(Master!\$J\$2:\$J\$48577=S2))Only S2 will change when you drag this.As hinted at above, you can lock just the Column or just the Row.When entering the formula, if you highlight the range e.g. B2:B48577 and press F4, it will rotate through the different Absolute and Relative reference choices.As far as dragging the formula to pick up different dates, why not put your 55 dates in a list and reference that list?=SUMPRODUCT((Master!\$B\$2:\$B\$48577=A1)*(Master!\$J\$2:\$J\$48577=\$S\$2))With a list of dates in A1:A55, you can drag this down and only A1 will change since it is the only Relative reference.

Report •

#2
September 29, 2010 at 08:40:14
 Hi DD03, thanks a lot on the clarification on my second question.on my first question.Instead of manually entering the dates I tried referencing it to the cell but that it returns with the 0... I am guessing the date formats can be pointed using cell references??

Report •

#3
September 29, 2010 at 08:44:33
 Maybe I'm missing something.Are you saying the "dates" in Master!\$B\$2:\$B\$48577 are not formatted as dates and therefore not matching the dates you listed in A1:A55?

Report •

Related Solutions

#4
September 29, 2010 at 09:58:44
 Ok got this working now... It was something to do with the formating. Now it's all good.thanks a lot for your help DD03

Report •

#5
September 29, 2010 at 10:12:39