View Single Post
  #1   Report Post  
tkacoo
 
Posts: n/a
Default

Thank you sooo much. I have been going crazy trying to work this out! I
used a little from each one and tailored it to my spreadsheet. It turned out
great and now it automatically creates a graph so that I can see the lastest
and greatest reports.
Again THANK YOU!!!!!!!

"Harlan Grove" wrote:

N Harkawat wrote...
This works for me to list all jobs that were open before Feb-1-2005

and are
either completed in Feb -2005 or are still in progress

=SUMPRODUCT(--('Master Sheet 1'!A2:A3000=A25),--('Master
Sheet1'!B2:B3000<=datevalue("02/01/2005")),--('Master
Sheet1'!C2:C3000=datevalue("02/01/2005")+isblank('Master

Sheet1'!C2:C3000))
....

There's a syntax error, so it wasn't tested. But it's close. If the
date threshold were entered in a cell named ByDate, make the formula

=SUMPRODUCT(--('Master Sheet 1'!A2:A3000=A25),
--('Master Sheet1'!B2:B3000<=ByDate),
('Master Sheet1'!C2:C3000=ByDate)+(TRIM('Master Sheet1'!C2:C3000)=""))

Also, reread the OP's specs and sample intended results. Following your
approach for Dec 2004, if ByDate were 1 Dec 2004, then the activity 100
begun on 25 Dec 2004 wouldn't be included in the Dec 2004 result. It's
pretty clear the OP's ByDate values are the last days rather than the
first days of the given months.