• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct between 2 dates including blank date

G

Gita

New Member
Hi

Hope you can help me with the right formula.

[pre]
Code: 
CaseNumber	Date Raised	Reason	Open Days	Date Closed
1		30-Oct-11	xxx	20		27-Nov-11
2		26-Mar-11	yyy	244		1-Mar-12
3	       	26-Mar-11	zzz	353
4		18-Apr-12	xxx	61		11-Jul-12
[/pre]
My question is how to count how many cases are still open when I enter in column F the month January 2012 (should come up with answer=3). and in column H for month March should come up with answer=2.

Does this make sense? Would be thankful for the suggestion.

Cheers!
 
Why would January be 3? only items 1-3 were opened before January, and 1 was closed before January. So, #4 wasn't "still open" in January 2012. Sounds like we may need to clarify what you mean by "still open". Here's a formula to at least start us off.


=SUMPRODUCT(($B$2:$B$5<F6)*(($E$2:$E$5>EOMONTH(F6,0))+($E$2:$E$5="")))
 
Hi Luke,


Sorry, you are correct, it was my error.

Here is the link https://docs.google.com/spreadsheet/ccc?key=0AlqBuIW7N7XQdDhaUERWOUw4aXBDTzBNQ3RKN1pJSEE

I used the formula in Cell Q8 & changed a bit into SUMPRODUCT(($B$7:$B$35<DATE(2012,1,1))*(($M$7:$M$35>EOMONTH(DATE(2012,1,30),0)+($M$7:$M$35="")))) and results came back as 2 when it should be 4 (B8 to B10 and B19). Would you have a look on the spreadsheet and let me know where the error is? Thanks a heap!
 
@Luke M

Hi!

Just because of your CASFFML excuse, here's the data:

-----

[pre]
Code: 
CPAR Number	Date Raised	Reason	"Open Days"	Date Closed

200695740	30-oct-11		20		27-nov-11
200736836	26-mar-11		244		1-mar-12
200736834	26-mar-11		353
200736820	26-mar-11		353
200742618	18-abr-12		61		11-jul-12
200742893	18-abr-12		61		11-jul-12
200747525	7-may-12		45		6-jul-12
200764008	6-jul-12		4		11-jul-12
200763989	6-jul-12		13		24-jul-12
200763990	6-jul-12		1		6-jul-12
200768898	24-jul-12		7
TEST		10-ene-12		147		1-ago-12
TEST		10-feb-11		385		1-ago-12
[/pre]
-----


Regards!
 
Thanks SirJB7!


@Gita

Looks like a parenthesis got misplaced. Your formula should be:

=SUMPRODUCT(($B$7:$B$35<DATE(2012,1,1))*(($M$7:$M$35>EOMONTH(DATE(2012,1,30),0))+($M$7:$M$35="")))


Note sure why you're putting the date in manually. A cell reference (say that Q7 has the date 1/1/2012) would be easier and would let you quickly apply same formula to different months.


=SUMPRODUCT(($B$7:$B$35<Q7)*(($M$7:$M$35>EOMONTH(Q7,0))+($M$7:$M$35="")))
 
@Luke M

Hi!

You're welcome.

Regards!


@SirJB7

Hi, myself!

So long...

Nice trick this of CASFFML, what if I too... :)

Regards!
 
Hi Luke, I added a column N which is the networkdays between Date Raised and Date Closed. I changed the formula into SUMPRODUCT(($n$7:$n$35>30),($B$7:$B$35<DATE(2012,1,1))*(($M$7:$M$35>EOMONTH(DATE(2012,1,30),0))+($M$7:$M$35=""))) to count which CPAR were open for more than 30days in January. But it is not working. What could possibly be the problem ? It is the same condition, just an extra criteria of checking data with networkdays more than 30days in month January. Thanks.
 
Hai..

I found a problem regarding this data


Start Finish

27-1-12 02-03-12

02-10-12

02-11-12 TBD

03-01-12

14-3-12 22-4-12

17-4-12 TBD

05-01-12

05-12-12 06-06-12

19-5-12 06-01-12


here I want to see the finished date for one month. The problem is there are characters and blanks

please its support for a suitable formula
 
You must log in or register to reply here.
Back
Top