Add criteria to sumproduct Greater than or equal to


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
 
Ad

Advertisements

M

Max

Think this part:
..,--((Data!$J$3:$J$10000=>I$6)+(Data!$J$3:$J$10000=<I$7)),..
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
---
 
J

Jacob Skaria

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
 
D

Diddy

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
 
D

Diddy

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
 
Ad

Advertisements

Joined
Apr 3, 2017
Messages
1
Reaction score
0
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top