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
 
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
 

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