Complex - Multiple Criteria

[dunmore83]

Excel 2012
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Ear Tag	Sex	NLIS		Birth Date				01-Jul-14	02-Jul-14	03-Jul-14	04-Jul-14	05-Jul-14	06-Jul-14	07-Jul-14	08-Jul-14
3
4
5	1658	Heifer	-	04.09.11	04-Sep-11				1,031	1,032	1,033	1,034	1,035	1,036	1,037	1,038
6	2112	Heifer	982123490405654	10.08.12	10-Aug-12				690	691	692	693	694	695	696	697
7	272	Heifer	982123490425668	27.08.12	27-Aug-12				673	674	675	676	677	678	679	680
8	2282	Heifer	982123490425757	28.09.12	28-Sep-12				641	642	643	644	645	646	647	648
9	3007	Heifer	982123490425176	24.01.13	24-Jan-13				523	524	525	526	527	528	529	530
10	3008	Heifer	982123490417932	24.01.13	24-Jan-13				523	524	525	526	527	528	529	530
11	3018	Heifer	982123490425858	29.01.13	29-Jan-13				518	519	520	521	522	523	524	525
12	3026	Heifer	982123498847561	31.01.13	31-Jan-13				516	517	518	519	520	521	522	523
13	3040	Heifer	982123490449063	06.02.13	06-Feb-13				510	511	512	513	514	515	516	517
14	3042	Heifer	982123490417737	06.02.13	06-Feb-13				510	511	512	513	514	515	516	517
15	3053	Heifer	982123490405560	07.02.13	07-Feb-13				509	510	511	512	513	514	515	516
16	3052	Heifer	982123490449125	07.02.13	07-Feb-13				509	510	511	512	513	514	515	516
17	3051	Heifer	982123489462454	07.02.13	07-Feb-13				509	510	511	512	513	514	515	516
18	3055	Heifer	982123490425967	08.02.13	08-Feb-13				508	509	510	511	512	513	514	515
19	3054	Heifer	982123490425848	08.02.13	08-Feb-13				508	509	510	511	512	513	514	515
20	3064	Heifer	982123490417515	11.02.13	11-Feb-13				505	506	507	508	509	510	511	512
21	3061	Heifer	982123490419708	11.02.13	11-Feb-13				505	506	507	508	509	510	511	512
22	3065	Heifer	982123498847685	12.02.13	12-Feb-13				504	505	506	507	508	509	510	511
23	3069	Heifer	982091016308716	14.02.13	14-Feb-13				502	503	504	505	506	507	508	509
24	3071	Heifer	982123490447819	14.02.13	14-Feb-13				502	503	504	505	506	507	508	509
25	3074	Heifer	982123490425039	17.02.13	17-Feb-13				499	500	501	502	503	504	505	506
26	3084	Heifer	982123490226691	26.02.13	26-Feb-13				490	491	492	493	494	495	496	497
27	3088	Heifer	982123490425140	28.02.13	28-Feb-13				488	489	490	491	492	493	494	495
28	3090	Heifer	982123489464042	03.03.13	03-Mar-13				485	486	487	488	489	490	491	492
29	3098	Heifer	982123489459356	11.03.13	11-Mar-13				477	478	479	480	481	482	483	484
30	3104	Heifer	982123489466360	01.04.13	01-Apr-13				456	457	458	459	460	461	462	463
31	3161	Heifer	982123490224041	09.08.13	09-Aug-13				326	327	328	329	330	331	332	333
32	3176	Heifer	982000191763119	15.08.13	15-Aug-13				320	321	322	323	324	325	326	327
33	3179	Heifer	982000191805821	16.08.13	16-Aug-13				319	320	321	322	323	324	325	326
34	3190	Heifer	982000191727512	18.08.13	18-Aug-13				317	318	319	320	321	322	323	324
35	3186	Heifer	982123498851839	18.08.13	18-Aug-13				317	318	319	320	321	322	323	324
36	3191	Heifer	982000191736709	19.08.13	19-Aug-13				316	317	318	319	320	321	322	323
ages-excel-forum

 

[dunmore83]

There will be numerous columns with min & max dates... for example another column might be 04-Sep-14 to 29-Jan-15 then in this situation the formula needs to behave exactly like you had previously.

It's just when a part of the min/max range falls before 01-July-14 that the modification needs to be made.

Make any sense ? If not I will post an example

 

[dunmore83]

Excel 2012
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
3
4
5
6										Start #			Start #			Start #
7
8
9							24-Jan-13			11-Jan-14			04-Sep-14			25-Mar-15
10
11
12							01-Oct-13			22-Apr-14			29-Jan-15			30-Jun-15
13
14
15							223			81			0			0
16	Jul-14			31			223			81			0			0
17	Aug-14			31			223			81			0			0
18	Sep-14			30			223			81			16			0
19	Oct-14			31			223			81			25			0
20	Nov-14			30			223			81			65			0
21	Dec-14			31			223			81			104			0
22	Jan-15			31			223			81			123			0
23	Feb-15			28			223			81			123			0
24	Mar-15			31			223			81			123			10
25	Apr-15			30			223			81			123			48
26	May-15			31			223			81			123			85
27	Jun-15			30			223			81			123			107
Stock-Summary-forum

 

[dunmore83]

The formula in row 15 is =COUNTIFS(Sheet1!$F$4:$F$667,">="&J$9,Sheet1!$F$4:$F$667,"<="&$D16)

Problem is this is assuming everything prior to 01-Jul-14 will go into the 1 group of numbers i.e. column J

However there will be numerous groups prior to 01-Jul-14 that need to be treated separately based on their min & max dates

 

[gaz_chops]

And if you change the date reference to J$12 do you get the right result?

=COUNTIFS(Sheet1!$F$4:$F$667,">="&J$9,Sheet1!$F$4:$F$667,"<="&$J$12)

 

[dunmore83]

Yes until the July-14 year begins.. problem is there the formula sums everything between those dates. However it needs to follow the pattern of accumulating numbers when the July-14 year begins for those min & max values that fall within the Jul-14 to Jun-15 year

 

[gaz_chops]

I think i get it!!

This will test the date if less than 1/7/14 it uses the Max date if greater than the 1/7/14 it uses 1/7/14

=IF(K12<$E$15,COUNTIFS(Sheet1!$F$4:$F$659,">="&K$9,Sheet1!$F$4:$F$659,"<="&K$12),COUNTIFS(Sheet1!$F$4:$F$659,">="&K$9,Sheet1!$F$4:$F$659,"<="&$E$15))

 

[dunmore83]

Sorry this is so confusing!!!

Does this formula to go in rows Jul-14 to Jun-15 or do we still need a separate formula in the row above Jun-14 ?

 

[gaz_chops]

K14

=IF(K12<$E$15,COUNTIFS(Sheet1!$F$4:$F$659,">="&K$9,Sheet1!$F$4:$F$659,"<="&K$12),COUNTIFS(Sheet1!$F$4:$F$659,">="&K$9,Sheet1!$F$4:$F$659,"<="&$E$15))

K15 copied down


=K14+SUMPRODUCT((TEXT(Sheet1!$F$4:$F$659,"mmyy")=TEXT($E15,"mmyy"))*(Sheet1!$F$4:$F$659>=K$9)*(Sheet1!$F$4:$F$659<=K$12))

 

[dunmore83]

Thanks. Finally got there!!

Now I need to follow that on to work out the average age if you are interested?!

 

[dunmore83]

Excel 2012
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	Ear Tag	Sex	NLIS		Birth Date				10-Sep-14	11-Sep-14	12-Sep-14	13-Sep-14	14-Sep-14	15-Sep-14
3
4
5	4108	Heifer	982091014853941	18.04.14	18-Apr-14				145	146	147	148	149	150
6	4109	Heifer	982091014840877	19.04.14	19-Apr-14				144	145	146	147	148	149
7	4110	Heifer	982125000934585	19.04.14	19-Apr-14				144	145	146	147	148	149
8	4113	Heifer	982091014840710	20.04.14	20-Apr-14				143	144	145	146	147	148
9	4114	Heifer	982091014820378	22.04.14	22-Apr-14				141	142	143	144	145	146
10	4115	Heifer	982091016306527	04.09.14	04-Sep-14				6	7	8	9	10	11
11	4116	Heifer	982091016308968	10.09.14	10-Sep-14				0	1	2	3	4	5
12	4117	Heifer	982091016305006	11.09.14	11-Sep-14				0	0	1	2	3	4
13	4118	Heifer	982091016304286	13.09.14	13-Sep-14				0	0	0	0	1	2
Sheet1-forum