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

 

[gaz_chops]

If you are finding it slow let me know BEFORE you amend anything, I have created a new version without the Dynamic ranges which I will upload.

 

[dunmore83]

Thanks for that.

I apologize that we have another misunderstanding on this one!!! Please stay with me...

I realize that there are -84 between 1/3/15 & 30/6/15 however this not the correct way I need the formula to work. In this example it needs to search for data between the birth dates first which is 1/3/15 & 30/6/15.... then if any of these birth dates have dates in the preg column before 1/7/15 then these need to be summed in AT19.

Does this make any sense or is it still clear as mud?!!!

 

[gaz_chops]

No need to apologise, just want to make sure I get it right

Before I check and change anything, let me know about the speed of the last upload with the Dynamic Ranges? I can then work on the correct version.

 

[dunmore83]

Downloaded quicker & seemed faster. Thanks

 

[gaz_chops]

So if the Birth Date is between 1/3/15 & 30/6/15 AND the Death (Preg, Sold, Cull, Calve) date is between 1/3/15 & 30/6/15, then they should be counted?

I noticed the "Total Opening Births" is showing the same No.s for Heifers and Bulls! Should it reference the Sex so the count is for each not both?

 

[dunmore83]

Not quite... if the birth date is between 1/3/15 & 30/6/15 AND the death (preg, sold, cull, calve) date occurs in any of the months from July to Jun (I guess we are in the 2015-16 file) then it should be counted

For the carry-over line it is if the birth date is between 1/3/15 & 30/6/15 AND the death (preg, sold, cull, calve) is prior to 1/7/15 then they should be counted

I left it that way for total opening births so it counts the still borns as the sex of those is not known

 

[gaz_chops]

Ok got it, leave it with me.

 

[dunmore83]

Ok thanks.

I need to leave my computer for a while.

If you get a moment can you please look at my last post from this thread? Thanks

http://www.mrexcel.com/forum/excel-questions/871314-index-isnumber-data-anaysis-9.html#post4233838

 

[gaz_chops]

Best to sort this one 1st ?

 

[gaz_chops]

I have amended the Opening formulas for Heifers & Bulls and checked the other formulas, hopefully its what you want.
NOTE : I have only done it for #1 on both sheets, so you will need to copy down and adjust the date ranges.

I also changed the Dynamic Ranges to normal Named Ranges which I have set to Row 10,000, if or when you go beyond this you will simply need to change each of the Named Ranges in the Name Manager, if you don't know how to, let me know.

I also amended the 1st Date on each of the 1st 3 sheets to reference AX7, so you only need to change this date, the others will match.

Again, sense check the results and let me know, once you're happy we can look at the other Thread.


https://www.dropbox.com/s/jn4tk1ls896vzni/2015-16-Stock-Recon-V3.xlsx?dl=0