Adding date range to FREQUENCY function

[jray9242]

I have a frequency function that I need to add a date range as well to it. Can this be done?

Here is my formula.

B = MyDate

=SUM(IF(FREQUENCY(B8:B3000,B8:B3000)>0,1))

I need to add something like this added to my FREQUENCY Function:

>="&DATE(2011,1,1) and <= "&DATE(2011,3,31)

Thank you,

 

[doublej_jj]

Adding date range to FREQUENCY function
<HR style="BACKGROUND-COLOR: #ffffff; COLOR: #ffffff" SIZE=1>I have a frequency function that I need to add a date range as well to it. Can this be done?

Here is my formula.

B = MyDate

=SUM(IF(FREQUENCY(B8:B3000,B8:B3000)>0,1))

I need to add something like this added to my FREQUENCY Function:

>="&DATE(2011,1,1) and <= "&DATE(2011,3,31)

How about:

= Sumproduct((B2:B3000>=Date(2011,1,1))*(B2:B3000<=Date(2011,3,31))

I didn't try this, but I am fairly certain.

Jeff

 

[jray9242]

Perfect and I have another question as an add on to this one that I will post soon.

Thanks again!

 

[Aladin Akyurek]

I have a frequency function that I need to add a date range as well to it. Can this be done?

Here is my formula.

B = MyDate

=SUM(IF(FREQUENCY(B8:B3000,B8:B3000)>0,1))

This yields a count of unique dates.

I need to add something like this added to my FREQUENCY Function:

>="&DATE(2011,1,1) and <= "&DATE(2011,3,31)

Thank you,

These conditions would mean that you want to determine the number of unique dates between DATE(2011,1,1) and DATE(2011,3,31). Is this not what you want?

 

[jray9242]

Aladin Akyurek, You are correct.

Sorry for the confusion, this should help. It's been a long week.

Here is a better look at what I need and hope this is more clear what I need.

Under Date (B) I need to calc the value for the first quarter, The answer should be "3".

Thanks again for the help. All the answers have been great!

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N
3	BASIC
4	DATE	HOURS	AGENCY TYPE	LOCATION	TOPIC	EVENT TYPE	GIVEN BY(NAME)	STUDENTS TRAINED (#)	DELIVERY COST	B/O	OT	UASI / SHSGPCOST	NOTES
5
6	4	32.0	Pull Down					42	$3,600			Pull Down
7
8	1/21/2011	8.0	Health		Basic			5	400			UASI
9	1/21/2011	8.0	Health		Basic			5	400			SHSGP
10	1/21/2011	8.0	Other		Basic			6	400			UASI
11	2/22/2011	8.0	Health		Basic			5	400			UASI
12	2/22/2011	8.0	Other		Basic			6	400			UASI
13	3/22/2011	8.0	Health		Basic			5	400			UASI
14	3/22/2011	8.0	Other		Basic			6	400			UASI
15	4/22/2011	8.0	Health		Basic			2	400			UASI
16	4/22/2011	8.0	Other		Basic			2	400			UASI
Basic

 

[barry houdini]

Here's one way

=SUMPRODUCT((COUNTIF(B8:B1000,ROW(INDIRECT(DATE(2011,1,1)&":"&DATE(2011,3,31))))>0)+0)

that "hardcodes" the dates into the formula. Perhaps easier to put start date of the quarter in a cell, e.g. A1 and then use

=SUMPRODUCT((COUNTIF(B8:B1000,ROW(INDIRECT(A1&":"&EOMONTH(A1,2))))>0)+0)

 

[jray9242]

BINGO!

Thank you Barry! I never thought about using the INDIRECT function.

This is turning out to be a difficult project but the help here has been incredible and I am so much appreciative of all the help here.

Thanks again!

Jim