Add criteria to sumproduct Greater than or equal to

  • Thread starter Thread starter Diddy
  • Start date Start date
D

Diddy

Hi everyone,

I’m using the following

=SUMPRODUCT(--(Data!$C$3:$C$10000=$A8),--((Data!$J$3:$J$10000=>I$6)+(Data!$J$3:$J$10000=<I$7)),--((Data!$AC$3:$AC$10000="c")+(Data!$AC$3:$AC$10000="m")))

Where I6 and I7 are two dates including and between which the data should be
included.

I’m getting too many returned, so what am I doing wrong?

Any suggestions would be brilliant

Cheers
 
Think this part:
..,--((Data!$J$3:$J$10000=>I$6)+(Data!$J$3:$J$10000=<I$7)),..
Click to expand...
should be multiplicative (AND)

Try (untested)
=SUMPRODUCT((Data!$C$3:$C$10000=$A8)*(Data!$J$3:$J$10000>=I$6)*(Data!$J$3:$J$10000<=I$7)*((Data!$AC$3:$AC$10000="c")+(Data!$AC$3:$AC$10000="m")))
voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
 
Try

=SUMPRODUCT((Data!$C$3:$C$10000=$A8)*(Data!$J$3:$J$10000>=I$6)*(Data!$J$3:$J$10000<=I$7)*(Data!$AC$3:$AC$10000={"c","m"}))

If this post helps click Yes
 
Thank you Max just the job

Max said:
Think this part:
should be multiplicative (AND)

Try (untested):
=SUMPRODUCT((Data!$C$3:$C$10000=$A8)*(Data!$J$3:$J$10000>=I$6)*(Data!$J$3:$J$10000<=I$7)*((Data!$AC$3:$AC$10000="c")+(Data!$AC$3:$AC$10000="m")))
voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
Click to expand...
 
Thank you Jacob That did the trick :-)

Jacob Skaria said:
Try

=SUMPRODUCT((Data!$C$3:$C$10000=$A8)*(Data!$J$3:$J$10000>=I$6)*(Data!$J$3:$J$10000<=I$7)*(Data!$AC$3:$AC$10000={"c","m"}))

If this post helps click Yes
Click to expand...
 
Hi,
I'm using this formula with a worksheet:

=SUMPRODUCT(($B$3:$B$1000=100100)*($D$3:$D$1000>=$V$1)*($E$3:$E$1000="Buy")*(ISNUMBER(SEARCH($W$1,$H$3:$H$1000)))*($F$3:$F$1000))

The range in red is the date and time column while the $V$1 is where we input the date and time to calculate after. I need to add for the same range a date and time value $V$2 where the $D$3:$D$1000<=$V$2 to lock the time period I need for my calculations.

I'd appreciate your help..

Thanks
 
Back
Top