SUMPRODUCT or COUNTIFS

[nmss18]

Hello,
In the table below, in D2 what formula can I use to count the number of blank cells between L2 and S2 (which is when the user signed on as noted in columns B and C)?
and in D3 the formula should count the number of blank cells between F3 and M3.
So for User001, I would like to see 1 (as 2014 october is blank) and User002, there are 3 blank cells in F:M

I am using the countif formula =COUNTIF(E2:S2,"") but it is counting the entire range E:S and that is not what I want.

(I posted a similar posting earlier but I suspect it was too wordy and complex).

Any help would be appreciated.
nmss

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	User	Live Date	Cancel Date	Dormancy	2014 January	2014 February	2014 March	2014 April	2014 May	2014 June	2014 July	2014 August	2014 September	2014 October	2014 November	2014 December	2015 January	2015 February	2015 March
2	User001	2014 August	2015 March	8								1	1		1	1	1	1	1
3	user002	2014 February	2014 September	10		1	1	1			1		1
Sheet3

Cell Formulas
Range	Formula
D2	=COUNTIF(E2:S2,"")
D3	=COUNTIF(E3:S3,"")

 

[AhoyNC]

Try
For this to work your dates are going to have to be formatted as dates and not text (which they seem to be now).

You also shoukd stay with just the one post in the forum and add to it.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	User	Live Date	Cancel Date	Dormancy	January-2014	February-2014	March-2014	April-2014	May-2014	June-2014	July-2014	August-2014	September-2014	October-2014	November-2014
2	User001	August-2014	March-2015	1								1	1		1
3	user002	February-2014	September-2014	3		1	1	1			1		1
Sheet

 

[nmss18]

Worked perfectly, and i will follow your advice next time.
Thanks,
NMSS

 

[AhoyNC]

You're welcome. Thanks for the feedback.

 

[nmss18]

AhoyNC,
One more thing. I am trying to look at this from the monthly perspective as well. For each month how many users who were live had a dormant activity month.. I tried to modify your formula as follows in column H, but I am doing something wrong here:
=COUNTIFS($H$10,"<="&$B$11:$B$17,$H$10,"<="&$C$11:$C$17,H11:H17,"")

Could you assist?
Very much appreciated.
nmss

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1							Dormant by Month
2							Institutional	#VALUE!
3							Content	#VALUE!
4							Retail	#VALUE!
5							Unknown	#VALUE!
6								#VALUE!
7						Fiscal Date Hierarchy
8
9						Volume Executed		Fiscal Month
10	Classification	FC_Datevalue	LC_Datevalue	Connection Status	Trading Dormancy	Trading Broker	End User	Jul-14	Aug-14	Sep-14	Oct-14	Nov-14	Dec-14	Jan-15	Feb-15	Mar-15
11	Institutional	Jul-14	Oct-14	Active	4	DB	user0014							20,000		32,215
12	Institutional	Sep-14	Mar-15	Active	1	AKJ	user0015			5,854,352	602,007		24,000	4,300	4,600	215
13	Retail	Jul-14	Mar-15	Active	0	AKJ	user0016	461,248	454,908	461,884	275,461	720,921	252,061	120,032	33,016	87,298
14	Retail	Jan-13	Aug-14	Cancelled	0	AKJ	user0017	165,386	60,813
15	Retail	Jan-13	Feb-15	Cancelled	0	GS	user0018	248,113	262,980	491,906	94,236	115,610	104,676	177,609	191,438
16	Unknown	Jan-13	Mar-15	Active	5	AKJ	user0019	6,960	5,888	3,000						1,323
17	Unknown	Sep-13	Mar-15	Active	9	AKJ	user0020
18
Sheet1

Cell Formulas
Range	Formula
H2	=COUNTIFS($G$2:$G$5,G2,$H$10,"<="&$B$11:$B$17,$H$10,"<="&$C$11:$C$17,H7:H13,"""")
H3	=COUNTIFS($G$2:$G$5,G3,$H$10,"<="&$B$11:$B$17,$H$10,"<="&$C$11:$C$17,H8:H14,"""")
H4	=COUNTIFS($G$2:$G$5,G4,$H$10,"<="&$B$11:$B$17,$H$10,"<="&$C$11:$C$17,H9:H15,"""")
H5	=COUNTIFS($G$2:$G$5,G5,$H$10,"<="&$B$11:$B$17,$H$10,"<="&$C$11:$C$17,H10:H16,"""")
H6	=COUNTIFS($H$10,"<="&$B$11:$B$17,$H$10,"<="&$C$11:$C$17,H11:H17,"")
B12	=J10
E11	=COUNTIFS($H$10:$P$10,">="&B11,$H$10:$P$10,"<="&C11,H11:P11,"")
E12	=COUNTIFS($H$10:$P$10,">="&B12,$H$10:$P$10,"<="&C12,H12:P12,"")
E13	=COUNTIFS($H$10:$P$10,">="&B13,$H$10:$P$10,"<="&C13,H13:P13,"")
E14	=COUNTIFS($H$10:$P$10,">="&B14,$H$10:$P$10,"<="&C14,H14:P14,"")
E15	=COUNTIFS($H$10:$P$10,">="&B15,$H$10:$P$10,"<="&C15,H15:P15,"")
E16	=COUNTIFS($H$10:$P$10,">="&B16,$H$10:$P$10,"<="&C16,H16:P16,"")
E17	=COUNTIFS($H$10:$P$10,">="&B17,$H$10:$P$10,"<="&C17,H17:P17,"")

 

[AhoyNC]

Like this?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1							Dormant by Month
2							Institutional	1	1	1	1	1	0	0	0
3							Content	0	0	0	0	0	0	0	0
4							Retail	0	0	0	0	0	0	0	0
5							Unknown	1	1	1	2	2	2	2	2
6
7						Fiscal Date Hierarchy
8
9						Volume Executed		Fiscal Month
10	Classification	FC_Datevalue	LC_Datevalue	Connection Status	Trading Dormancy	Trading Broker	End User	Jul-2014	Aug-2014	Sep-2014	Oct-2014	Nov-2014	Dec-2014	Jan-2015	Feb-2015
11	Institutional	Jul-2014	Oct-2014	Active	4	DB	user0014							20,000
12	Institutional	Sep-2014	Mar-2015	Active	1	AKJ	user0015			5,854,352	602,007		24,000	4,300	4,600
13	Retail	Jul-2014	Mar-2015	Active	0	AKJ	user0016	461,248	454,908	461,884	275,461	720,921	252,061	120,032	33,016
14	Retail	Jan-2013	Aug-2014	Cancelled	0	AKJ	user0017	165,386	60,813
15	Retail	Jan-2013	Feb-2015	Cancelled	0	GS	user0018	248,113	262,980	491,906	94,236	115,610	104,676	177,609	191,438
16	Unknown	Jan-2013	Mar-2015	Active	5	AKJ	user0019	6,960	5,888	3,000
17	Unknown	Jan-2013	Mar-2015	Active	9	AKJ	user0020
Sheet

 

[nmss18]

Bizarre. In my test sheet this works but in my live data all I get is a 0 value.
I know the date formats are correct because I modified your original formula to fit my live sheet and it works.

Thsi is the formula I am using in the live data
=COUNTIFS($F$13:$F$8707,">="&L$10,$G$13:$G$8707,"<="&L$10,L13:L8707,"")

Its your formula modified. I took out 2 criteria ranges as they are not crucial. Do you see anything amiss?

 

[nmss18]

I will provide a portion of the live data and hide the 3 sensitive data columns

(Sorry for bombarding you with all the formulas. Not too sure why its doing that)

Excel 2012
	C	D	E	F	G	H	I	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
3
4
5
6
7
8								0
9
10								January-13	February-13	March-13	April-13	May-13	June-13	July-13	August-13	September-13	October-13	November-13	December-13	January-14	February-14	March-14	April-14	May-14	June-14	July-14	August-14	September-14	October-14	November-14	December-14	January-15	February-15	March-15
11								Fiscal Month
12	Classification	First Connection Date	Last Connection Date	FC_Datevalue	LC_Datevalue	Connection Status	Trading Dormancy	2013 January	2013 February	2013 March	2013 April	2013 May	2013 June	2013 July	2013 August	2013 September	2013 October	2013 November	2013 December	2014 January	2014 February	2014 March	2014 April	2014 May	2014 June	2014 July	2014 August	2014 September	2014 October	2014 November	2014 December	2015 January	2015 February	2015 March
13	Institutional	2013 January	2015 March	Jan-13	Mar-15	Active	5	724,824	528,145	256,422	136,900	277,424	159,031	228,300	266,517	230,000	380,856	309,907	326,150	631,843	172,110	92,200	201,160	142,730		436,355	459,000	488,005	390,933			498,346
14	Institutional	2014 February	2015 March	Feb-14	Mar-15	Active	4															37,541	173,202	15,534		20,000	8,003	35,879	37,746			16,000	75,120
15	Institutional	2013 January	2015 March	Jan-13	Mar-15	Active	21				244,500		40,000				82,000						18,000					100,000
16	Institutional	2014 February	2015 March	Feb-14	Mar-15	Active	0														37,048	37,810	319,710	3,120,013	564,818	331,117	293,173	392,221	306,690	439,500	82,500	7,541	248,647	364,898
17	Institutional	2013 January	2015 March	Jan-13	Mar-15	Active	26					50,000
18	Institutional	2013 January	2015 March	Jan-13	Mar-15	Active	16	2,431	72	469	947	800	2,181	6,000	1,711	2,589		10,000							128,699
19	Institutional	2013 January	2015 March	Jan-13	Mar-15	Active	26		54,827
20	Institutional	2014 June	2015 March	Jun-14	Mar-15	Active	2																			1,167,228	630,747	3,155,000	2,187,335	4,735,605		45,000	150,000	90,271
21	Institutional	2013 January	2015 March	Jan-13	Mar-15	Active	16	2,211	425,982	40	6,044	3,071	4,000		6,300														632,446	55,325		2,560,735	202,482
22	Institutional	2013 January	2015 March	Jan-13	Mar-15	Active	3		672,146	918,867	52,918	4,479,565	2,276,932			540,963	1,929,418	1,971,944	689,611	858,505	2,086,867	1,179,120	520,497	2,130,112	811,054	4,712,919	3,547,176	464,980	740,451	253,604	293,824	22,200	64,230	255,200
23	Institutional	2014 July	2015 March	Jul-14	Mar-15	Active	4																					63,023	318,006			11,679	33,676	264,729
24	Institutional	2013 October	2015 March	Oct-13	Mar-15	Active	17																					49,000
25	Institutional	2013 January	2015 March	Jan-13	Mar-15	Active	0	3,886,674	140,069	287,922	3,025,710	10,213,652	311,785	646,740	703,124	4,088,584	1,143,111	1,547,617	2,142,179	10,019,360	6,177,505	3,428,619	962,576	1,498,230	6,307,548	65,409	3,153,612	2,829,254	264,025	9,672,296	6,296,797	946,788	230,231	121,707
26								4,616,140	1,821,241	1,463,720	3,467,019	15,024,512	2,793,929	881,040	977,652	4,862,136	3,535,385	3,839,468	3,157,940	11,509,708	8,473,530	4,775,290	2,195,145	6,906,619	7,812,119	6,733,028	8,091,711	7,577,362	4,877,632	15,156,330	6,673,121	4,108,289	1,004,386	1,096,805
Trading Activity (2)

Cell Formulas
Range	Formula
C13	=VLOOKUP(J13,'Broker Tier Classification'!$H$1:$J$304,3,FALSE)
C14	=VLOOKUP(J14,'Broker Tier Classification'!$H$1:$J$304,3,FALSE)
C15	=VLOOKUP(J15,'Broker Tier Classification'!$H$1:$J$304,3,FALSE)
C16	=VLOOKUP(J16,'Broker Tier Classification'!$H$1:$J$304,3,FALSE)
C17	=VLOOKUP(J17,'Broker Tier Classification'!$H$1:$J$304,3,FALSE)
C18	=VLOOKUP(J18,'Broker Tier Classification'!$H$1:$J$304,3,FALSE)
C19	=VLOOKUP(J19,'Broker Tier Classification'!$H$1:$J$304,3,FALSE)
C20	=VLOOKUP(J20,'Broker Tier Classification'!$H$1:$J$304,3,FALSE)
C21	=VLOOKUP(J21,'Broker Tier Classification'!$H$1:$J$304,3,FALSE)
C22	=VLOOKUP(J22,'Broker Tier Classification'!$H$1:$J$304,3,FALSE)
C23	=VLOOKUP(J23,'Broker Tier Classification'!$H$1:$J$304,3,FALSE)
C24	=VLOOKUP(J24,'Broker Tier Classification'!$H$1:$J$304,3,FALSE)
C25	=VLOOKUP(J25,'Broker Tier Classification'!$H$1:$J$304,3,FALSE)
D13	=IFERROR(VLOOKUP($B13,'First-Last Terminal Date'!$B$7:$D$13277,3,FALSE),"")
D14	=IFERROR(VLOOKUP($B14,'First-Last Terminal Date'!$B$7:$D$13277,3,FALSE),"")
D15	=IFERROR(VLOOKUP($B15,'First-Last Terminal Date'!$B$7:$D$13277,3,FALSE),"")
D16	=IFERROR(VLOOKUP($B16,'First-Last Terminal Date'!$B$7:$D$13277,3,FALSE),"")
D17	=IFERROR(VLOOKUP($B17,'First-Last Terminal Date'!$B$7:$D$13277,3,FALSE),"")
D18	=IFERROR(VLOOKUP($B18,'First-Last Terminal Date'!$B$7:$D$13277,3,FALSE),"")
D19	=IFERROR(VLOOKUP($B19,'First-Last Terminal Date'!$B$7:$D$13277,3,FALSE),"")
D20	=IFERROR(VLOOKUP($B20,'First-Last Terminal Date'!$B$7:$D$13277,3,FALSE),"")
D21	=IFERROR(VLOOKUP($B21,'First-Last Terminal Date'!$B$7:$D$13277,3,FALSE),"")
D22	=IFERROR(VLOOKUP($B22,'First-Last Terminal Date'!$B$7:$D$13277,3,FALSE),"")
D23	=IFERROR(VLOOKUP($B23,'First-Last Terminal Date'!$B$7:$D$13277,3,FALSE),"")
D24	=IFERROR(VLOOKUP($B24,'First-Last Terminal Date'!$B$7:$D$13277,3,FALSE),"")
D25	=IFERROR(VLOOKUP($B25,'First-Last Terminal Date'!$B$7:$D$13277,3,FALSE),"")
E13	=IFERROR(VLOOKUP($B13,'First-Last Terminal Date'!$B$7:$E$13277,4,FALSE),"")
E14	=IFERROR(VLOOKUP($B14,'First-Last Terminal Date'!$B$7:$E$13277,4,FALSE),"")
E15	=IFERROR(VLOOKUP($B15,'First-Last Terminal Date'!$B$7:$E$13277,4,FALSE),"")
E16	=IFERROR(VLOOKUP($B16,'First-Last Terminal Date'!$B$7:$E$13277,4,FALSE),"")
E17	=IFERROR(VLOOKUP($B17,'First-Last Terminal Date'!$B$7:$E$13277,4,FALSE),"")
E18	=IFERROR(VLOOKUP($B18,'First-Last Terminal Date'!$B$7:$E$13277,4,FALSE),"")
E19	=IFERROR(VLOOKUP($B19,'First-Last Terminal Date'!$B$7:$E$13277,4,FALSE),"")
E20	=IFERROR(VLOOKUP($B20,'First-Last Terminal Date'!$B$7:$E$13277,4,FALSE),"")
E21	=IFERROR(VLOOKUP($B21,'First-Last Terminal Date'!$B$7:$E$13277,4,FALSE),"")
E22	=IFERROR(VLOOKUP($B22,'First-Last Terminal Date'!$B$7:$E$13277,4,FALSE),"")
E23	=IFERROR(VLOOKUP($B23,'First-Last Terminal Date'!$B$7:$E$13277,4,FALSE),"")
E24	=IFERROR(VLOOKUP($B24,'First-Last Terminal Date'!$B$7:$E$13277,4,FALSE),"")
E25	=IFERROR(VLOOKUP($B25,'First-Last Terminal Date'!$B$7:$E$13277,4,FALSE),"")
F13	=IFERROR(DATEVALUE(CONCATENATE(MID(D13,6,10),LEFT(D13,4))),"")
F14	=IFERROR(DATEVALUE(CONCATENATE(MID(D14,6,10),LEFT(D14,4))),"")
F15	=IFERROR(DATEVALUE(CONCATENATE(MID(D15,6,10),LEFT(D15,4))),"")
F16	=IFERROR(DATEVALUE(CONCATENATE(MID(D16,6,10),LEFT(D16,4))),"")
F17	=IFERROR(DATEVALUE(CONCATENATE(MID(D17,6,10),LEFT(D17,4))),"")
F18	=IFERROR(DATEVALUE(CONCATENATE(MID(D18,6,10),LEFT(D18,4))),"")
F19	=IFERROR(DATEVALUE(CONCATENATE(MID(D19,6,10),LEFT(D19,4))),"")
F20	=IFERROR(DATEVALUE(CONCATENATE(MID(D20,6,10),LEFT(D20,4))),"")
F21	=IFERROR(DATEVALUE(CONCATENATE(MID(D21,6,10),LEFT(D21,4))),"")
F22	=IFERROR(DATEVALUE(CONCATENATE(MID(D22,6,10),LEFT(D22,4))),"")
F23	=IFERROR(DATEVALUE(CONCATENATE(MID(D23,6,10),LEFT(D23,4))),"")
F24	=IFERROR(DATEVALUE(CONCATENATE(MID(D24,6,10),LEFT(D24,4))),"")
F25	=IFERROR(DATEVALUE(CONCATENATE(MID(D25,6,10),LEFT(D25,4))),"")
G13	=IFERROR(DATEVALUE(CONCATENATE(MID(E13,6,10),LEFT(E13,4))),"")
G14	=IFERROR(DATEVALUE(CONCATENATE(MID(E14,6,10),LEFT(E14,4))),"")
G15	=IFERROR(DATEVALUE(CONCATENATE(MID(E15,6,10),LEFT(E15,4))),"")
G16	=IFERROR(DATEVALUE(CONCATENATE(MID(E16,6,10),LEFT(E16,4))),"")
G17	=IFERROR(DATEVALUE(CONCATENATE(MID(E17,6,10),LEFT(E17,4))),"")
G18	=IFERROR(DATEVALUE(CONCATENATE(MID(E18,6,10),LEFT(E18,4))),"")
G19	=IFERROR(DATEVALUE(CONCATENATE(MID(E19,6,10),LEFT(E19,4))),"")
G20	=IFERROR(DATEVALUE(CONCATENATE(MID(E20,6,10),LEFT(E20,4))),"")
G21	=IFERROR(DATEVALUE(CONCATENATE(MID(E21,6,10),LEFT(E21,4))),"")
G22	=IFERROR(DATEVALUE(CONCATENATE(MID(E22,6,10),LEFT(E22,4))),"")
G23	=IFERROR(DATEVALUE(CONCATENATE(MID(E23,6,10),LEFT(E23,4))),"")
G24	=IFERROR(DATEVALUE(CONCATENATE(MID(E24,6,10),LEFT(E24,4))),"")
G25	=IFERROR(DATEVALUE(CONCATENATE(MID(E25,6,10),LEFT(E25,4))),"")
H13	=IFERROR(VLOOKUP('Trading Activity (2)'!B13,'First-Last Terminal Date'!$B$7:$H$13277,7,FALSE),"")
H14	=IFERROR(VLOOKUP('Trading Activity (2)'!B14,'First-Last Terminal Date'!$B$7:$H$13277,7,FALSE),"")
H15	=IFERROR(VLOOKUP('Trading Activity (2)'!B15,'First-Last Terminal Date'!$B$7:$H$13277,7,FALSE),"")
H16	=IFERROR(VLOOKUP('Trading Activity (2)'!B16,'First-Last Terminal Date'!$B$7:$H$13277,7,FALSE),"")
H17	=IFERROR(VLOOKUP('Trading Activity (2)'!B17,'First-Last Terminal Date'!$B$7:$H$13277,7,FALSE),"")
H18	=IFERROR(VLOOKUP('Trading Activity (2)'!B18,'First-Last Terminal Date'!$B$7:$H$13277,7,FALSE),"")
H19	=IFERROR(VLOOKUP('Trading Activity (2)'!B19,'First-Last Terminal Date'!$B$7:$H$13277,7,FALSE),"")
H20	=IFERROR(VLOOKUP('Trading Activity (2)'!B20,'First-Last Terminal Date'!$B$7:$H$13277,7,FALSE),"")
H21	=IFERROR(VLOOKUP('Trading Activity (2)'!B21,'First-Last Terminal Date'!$B$7:$H$13277,7,FALSE),"")
H22	=IFERROR(VLOOKUP('Trading Activity (2)'!B22,'First-Last Terminal Date'!$B$7:$H$13277,7,FALSE),"")
H23	=IFERROR(VLOOKUP('Trading Activity (2)'!B23,'First-Last Terminal Date'!$B$7:$H$13277,7,FALSE),"")
H24	=IFERROR(VLOOKUP('Trading Activity (2)'!B24,'First-Last Terminal Date'!$B$7:$H$13277,7,FALSE),"")
H25	=IFERROR(VLOOKUP('Trading Activity (2)'!B25,'First-Last Terminal Date'!$B$7:$H$13277,7,FALSE),"")
I13	=COUNTIFS($L$10:$AL$10,">="&F13,$L$10:$AL$10,"<="&G13,L13:AL13,"")
I14	=COUNTIFS($L$10:$AL$10,">="&F14,$L$10:$AL$10,"<="&G14,L14:AL14,"")
I15	=COUNTIFS($L$10:$AL$10,">="&F15,$L$10:$AL$10,"<="&G15,L15:AL15,"")
I16	=COUNTIFS($L$10:$AL$10,">="&F16,$L$10:$AL$10,"<="&G16,L16:AL16,"")
I17	=COUNTIFS($L$10:$AL$10,">="&F17,$L$10:$AL$10,"<="&G17,L17:AL17,"")
I18	=COUNTIFS($L$10:$AL$10,">="&F18,$L$10:$AL$10,"<="&G18,L18:AL18,"")
I19	=COUNTIFS($L$10:$AL$10,">="&F19,$L$10:$AL$10,"<="&G19,L19:AL19,"")
I20	=COUNTIFS($L$10:$AL$10,">="&F20,$L$10:$AL$10,"<="&G20,L20:AL20,"")
I21	=COUNTIFS($L$10:$AL$10,">="&F21,$L$10:$AL$10,"<="&G21,L21:AL21,"")
I22	=COUNTIFS($L$10:$AL$10,">="&F22,$L$10:$AL$10,"<="&G22,L22:AL22,"")
I23	=COUNTIFS($L$10:$AL$10,">="&F23,$L$10:$AL$10,"<="&G23,L23:AL23,"")
I24	=COUNTIFS($L$10:$AL$10,">="&F24,$L$10:$AL$10,"<="&G24,L24:AL24,"")
I25	=COUNTIFS($L$10:$AL$10,">="&F25,$L$10:$AL$10,"<="&G25,L25:AL25,"")
L8	=COUNTIFS($F$13:$F$25,">="&L$10,$G$13:$G$25,"<="&L$10,L13:L25,"")
L10	=DATEVALUE(CONCATENATE(MID(L$12,6,10),"-",LEFT(L$12,4)))
M10	=DATEVALUE(CONCATENATE(MID(M$12,6,10),"-",LEFT(M$12,4)))
N10	=DATEVALUE(CONCATENATE(MID(N$12,6,10),"-",LEFT(N$12,4)))
O10	=DATEVALUE(CONCATENATE(MID(O$12,6,10),"-",LEFT(O$12,4)))
P10	=DATEVALUE(CONCATENATE(MID(P$12,6,10),"-",LEFT(P$12,4)))
Q10	=DATEVALUE(CONCATENATE(MID(Q$12,6,10),"-",LEFT(Q$12,4)))
R10	=DATEVALUE(CONCATENATE(MID(R$12,6,10),"-",LEFT(R$12,4)))
S10	=DATEVALUE(CONCATENATE(MID(S$12,6,10),"-",LEFT(S$12,4)))
T10	=DATEVALUE(CONCATENATE(MID(T$12,6,10),"-",LEFT(T$12,4)))
U10	=DATEVALUE(CONCATENATE(MID(U$12,6,10),"-",LEFT(U$12,4)))
V10	=DATEVALUE(CONCATENATE(MID(V$12,6,10),"-",LEFT(V$12,4)))
W10	=DATEVALUE(CONCATENATE(MID(W$12,6,10),"-",LEFT(W$12,4)))
X10	=DATEVALUE(CONCATENATE(MID(X$12,6,10),"-",LEFT(X$12,4)))
Y10	=DATEVALUE(CONCATENATE(MID(Y$12,6,10),"-",LEFT(Y$12,4)))
Z10	=DATEVALUE(CONCATENATE(MID(Z$12,6,10),"-",LEFT(Z$12,4)))
AA10	=DATEVALUE(CONCATENATE(MID(AA$12,6,10),"-",LEFT(AA$12,4)))
AB10	=DATEVALUE(CONCATENATE(MID(AB$12,6,10),"-",LEFT(AB$12,4)))
AC10	=DATEVALUE(CONCATENATE(MID(AC$12,6,10),"-",LEFT(AC$12,4)))
AD10	=DATEVALUE(CONCATENATE(MID(AD$12,6,10),"-",LEFT(AD$12,4)))
AE10	=DATEVALUE(CONCATENATE(MID(AE$12,6,10),"-",LEFT(AE$12,4)))
AF10	=DATEVALUE(CONCATENATE(MID(AF$12,6,10),"-",LEFT(AF$12,4)))
AG10	=DATEVALUE(CONCATENATE(MID(AG$12,6,10),"-",LEFT(AG$12,4)))
AH10	=DATEVALUE(CONCATENATE(MID(AH$12,6,10),"-",LEFT(AH$12,4)))
AI10	=DATEVALUE(CONCATENATE(MID(AI$12,6,10),"-",LEFT(AI$12,4)))
AJ10	=DATEVALUE(CONCATENATE(MID(AJ$12,6,10),"-",LEFT(AJ$12,4)))
AK10	=DATEVALUE(CONCATENATE(MID(AK$12,6,10),"-",LEFT(AK$12,4)))
AL10	=DATEVALUE(CONCATENATE(MID(AL$12,6,10),"-",LEFT(AL$12,4)))

 

[AhoyNC]

I think you just need to reverse your greater than & less than in your formula.

=COUNTIFS($F$13:$F$8707,"[COLOR=#ff0000]<=[/COLOR]"&L$10,$G$13:$G$8707,"[COLOR=#ff0000]>=[/COLOR]"&L$10,L13:L8707,"")

If that doesn't work, format your dates to show m/d/y to make sure they are correct (Excel sometimes tries to out guess you on what it thinks you want).

Excel Workbook
	C	D	E	F	G	H	I	L	M	N	O
8								4
9
10								Jan-2013	Feb-2013	Mar-2013	Apr-2013
11								Fiscal Month
12	Classification	First Connection Date	Last Connection Date	FC_Datevalue	LC_Datevalue	Connection Status	Trading Dormancy	2013 January	2013 February	2013 March	2013 April
13	Institutional	2013 January	2015 March	Jan-2013	Mar-2015	Active	5	724,824	528,145	256,422	136,900
14	Institutional	2014 February	2015 March	Feb-2014	Mar-2015	Active	4
15	Institutional	2013 January	2015 March	Jan-2013	Mar-2015	Active	21				244,500
16	Institutional	2014 February	2015 March	Feb-2014	Mar-2015	Active	0
17	Institutional	2013 January	2015 March	Jan-2013	Mar-2015	Active	26
18	Institutional	2013 January	2015 March	Jan-2013	Mar-2015	Active	16	2,431	72	469	947
19	Institutional	2013 January	2015 March	Jan-2013	Mar-2015	Active	26		54,827
20	Institutional	2014 June	2015 March	Jun-2014	Mar-2015	Active	2
21	Institutional	2013 January	2015 March	Jan-2013	Mar-2015	Active	16	2,211	425,982	40	6,044
22	Institutional	2013 January	2015 March	Jan-2013	Mar-2015	Active	3		672,146	918,867	52,918
23	Institutional	2014 July	2015 March	Jul-2014	Mar-2015	Active	4
24	Institutional	2013 October	2015 March	Oct-2013	Mar-2015	Active	17
25	Institutional	2013 January	2015 March	Jan-2013	Mar-2015	Active	0	3,886,674	140,069	287,922	3,025,710
Sheet

 

[nmss18]

That loks like it works but on the surface its counterintuitive to me.
I would think in order to determine whether a date like February 2013 falls between January 2013 and March 2015, I have to measure whether February 2013 is 'higher' than January 2013 and 'lower' than March 2015.
The formula is measuring this from the inverse.
Why does it work?
Thanks a gazillion for your help
nmss