Count no of dates

[Ranjjeetk]

I have data

Demo4.xls
	A	B	C	D	E	F	G	H
1	TicketNumber+	Impact	CreatedBy	Assigned-to	AssignTime	WIPTime	ClosedTime	Status
2	2917288	Medium	ci655	KhopdeRanjjeet	6/1/200621:21			PendingInfo
3	2894043	Medium	cs730	KhopdeRanjjeet	5/19/200619:03		5/25/200620:08	Closed
4	2698598	Medium	fz536	KhopdeRanjjeet	2/20/200614:31		4/18/200615:24	Closed
5	2686476	Medium	cd856	KhopdeRanjjeet	2/20/20063:59		2/20/20068:25	Closed
6	2593645	Medium	ay329	KhopdeRanjjeet	2/20/200614:07	12/8/200523:23	3/15/200616:29	Closed
7	2623070	Medium	cv056	KhopdeRanjjeet	3/7/200617:50	12/27/200519:38	5/3/200615:57	Closed
8	2622551	Medium	cv056	KhopdeRanjjeet	3/7/200617:50	12/27/200519:39	5/3/20066:29	Closed
Discoverer Data

And data I required should populate in

Demo4.xls
	A	B	C	D	E
4	FromDate	ToDate	TicketsAssigned	Closed	WIP
5	08-Oct-05	14-Oct-05
6	15-Oct-05	21-Oct-05
7	22-Oct-05	28-Oct-05
Stats

In second excel from date and to date contains the dates and under Ticket Assigned I should get count from excel 1 (Col Assign_time) count of ticket between those two dates. Kindly suggest.

 

[NBVC]

Try:

=SUMPRODUCT(--('Discoverer Data'!$E$2:$E$8>=A5),--('Discoverer Data'!$E$2:$E$8<=B5))

Copied down.

Adjust ranges to suit.

 

[goldfish]

I'm not sure what you mean by "between" since each ticket has 3 dates... but you can switch which columns in Discover Data this formula references to account for your needs:

=SUMPRODUCT(--(A1<'Discover Data'!E$1:E$50),--(B1>'Discover Data'!G$1:G$50))

Hope this helps,
~Goldfish

 

[Ranjjeetk]

It works for given range ie A2:A50 but if I want to give the range as comlete column ie A:A then how can I go about it . This I have to do bcoz on daily basis I need to add the data

 

[NBVC]

Full column references cannot be used in Sumproduct() formulas and I think all Array formulas, so you have to define the range.

If you have Excel 2003, you can use the List feature found in the Data menu to make a dynamic range.

 

[Ranjjeetk]

So is there any other formula other that sumproduct that can help me?

 

[NBVC]

Try Countif()

e.g. =Countif('Discoverer Data'!$E:$E,">="&A5)-Countif('Discoverer Data'!$E:$E,">"&B5)

 

[Ranjjeetk]

Thanks,

The formula =SUMPRODUCT(('Discoverer Data'!$E$2:$E$8>=A5)*('Discoverer Data'!$E$2:$E$8<=B5)) is working for me.

But if you see the excel image 1 and in that if I also want to add in above formula created by = ci655 or cs730 how should I go for it.

 

[NBVC]

Try:

=SUMPRODUCT(('Discoverer Data'!$E$2:$E$8>=A5)*('Discoverer Data'!$E$2:$E$8<=B5)*('Discoverer Data'!$C$2:$C$8="ci655")+('Discoverer Data'!$C$2:$C$8="cs730"))

 

[Ranjjeetk]

And what if all but excluding ci655 and cs730