SUMPRODUCT help....

C

Chip Smith

I figured out that the SUMPRODUCT formula would work best in my situation and
it did....however i made a slight error when writing it and now i'm
stuck...the currents formual looks like this...

=SUMPRODUCT(--(MONTH(Data!$G$2:$G$47)=2),--(YEAR(Data!$G$2:$G$47)=2006),--(Data!$D$2:$D$47="Purchased"))

however when dealing with my data, i need it to search between two
dates....for example the date would be anything between Jan 1, 2006 and Jan
20, 2006.WOuld the formual look somehting like this??

=SUMPRODUCT(--(DATE(2006,1,1<Data!$D$2:$D$47<2006,1,20)),--(Data!$D$2:$D$47="Purchased"))

Thank you!
 
D

daddylonglegs

You could use

=SUMPRODUCT(--(DATE(2006,1,1)<=Data!$G$2:$G$47),--(DATE(2006,1,20)>=Data!$G$2:$G$47),--(Data!$D$2:$D$47="Purchased"))

...or better yet, put your dates in cells, e.g. J2 and K2 and reference
those - makes it easier to change date ranges or use multiple ranges

=SUMPRODUCT(--(J2<=Data!$G$2:$G$47),--(K2>=Data!$G$2:$G$47),--(Data!$D$2:$D$47="Purchased"))
 
C

Chip Smith

the only problem that i see with that is that each month it will count all
the previous entries...becasue i am using this for each month of the
year...the 21st of the month before to the 20th of the current month all the
way from jan to dec
 
D

daddylonglegs

Chip said:
the only problem that i see with that is that each month it will count
all
the previous entries...becasue i am using this for each month of the
year...the 21st of the month before to the 20th of the current month
all the
way from jan to dec
Click to expand...

I'm not sure what you mean, it will only count entries between the two
dates specified where column D is "purchased".....

If you want you could put formulas in J2 and K2 to change the dates
automatically each month, on what date do you switch from 21st february
- 20th March and start looking at 21st March - 20th April?
 
Top