| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add criteria to sumproduct Greater than or equal to
Hi everyone,
Im 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. Im getting too many returned, so what am I doing wrong? Any suggestions would be brilliant Cheers |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add criteria to sumproduct Greater than or equal to
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 --- "Diddy" wrote: Im 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. Im getting too many returned, so what am I doing wrong? Any suggestions would be brilliant Cheers |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add criteria to sumproduct Greater than or equal to
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 --------------- Jacob Skaria "Diddy" wrote: Hi everyone, Im 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. Im getting too many returned, so what am I doing wrong? Any suggestions would be brilliant Cheers |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add criteria to sumproduct Greater than or equal to
Thank you Max just the job
"Max" wrote: 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 --- "Diddy" wrote: Im 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. Im getting too many returned, so what am I doing wrong? Any suggestions would be brilliant Cheers |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add criteria to sumproduct Greater than or equal to
Welcome, glad to hear
-- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Diddy" wrote in message ... Thank you Max just the job |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add criteria to sumproduct Greater than or equal to
Thank you Jacob That did the trick :-)
"Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Diddy" wrote: Hi everyone, Im 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. Im getting too many returned, so what am I doing wrong? Any suggestions would be brilliant Cheers |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Greater/Less Than or Equal To | Excel Discussion (Misc queries) | |||
| IF with Equal to or Greater than | Excel Discussion (Misc queries) | |||
| sort data rows "greater than or equal" criteria in another cell | Excel Worksheet Functions | |||
| GREATER OR EQUAL TO BUT LESS THAN Problem using Sumproduct | Excel Worksheet Functions | |||
| Vlookup but also equal to and greater than? | Excel Worksheet Functions | |||
Linear Mode
