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]

Ok I'm getting VERY confused!!!

It looks like I have copied the carry over line & all the formulas for the months.

I'm not sure what to do....???

 

[gaz_chops]

If you have then all the formula (not the carry overs), should look like below

=-SUMPRODUCT((TEXT(Deaths,"mmyy")=TEXT($D20,"mmyy"))*(DateCalved>=$V$1)*(DateCalved<=$V$2)*(Owner=$AH$10)*(Sex=$AH$13))

The 1st part should reference the Name Range as per the column header, the other 2 should reference the Name Range - DateCalved (Birth Day)

 

[dunmore83]

I'm pretty sure I do..

AN20
=-SUMPRODUCT((TEXT(Deaths,"mmyy")=TEXT($D20,"mmyy"))*(DateCalved>=$V$1)*(DateCalved<=$V$2)*(Owner=$AH$10)*(Sex=$AH$13))
AQ20
=-SUMPRODUCT((TEXT(Sold,"mmyy")=TEXT($D20,"mmyy"))*(DateCalved>=$V$1)*(DateCalved<=$V$2)*(Owner=$AH$10)*(Sex=$AH$13))
AT20
=-SUMPRODUCT((TEXT(Preg,"mmyy")=TEXT($D20,"mmyy"))*(DateCalved>=$V$1)*(DateCalved<=$V$2)*(Owner=$AH$10)*(Sex=$AH$13))
AW20
=-SUMPRODUCT((TEXT(Preg,"mmyy")=TEXT($D20,"mmyy"))*(DateCalved>=$V$1)*(DateCalved<=$V$2)*(Owner=$AH$10)*(Sex=$AH$13))

These are all copied down

Carry-over still not showing any data

 

[gaz_chops]

It won't till I fix it, upload the file so i can.

 

[dunmore83]

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

 

[gaz_chops]

There still not all done, look at Dun Deaths Row 63. And Row 106 for Sold & Preg.

I'll change the carry over, then upload so you can check again.

 

[gaz_chops]

Carry overs should now be correct, check you're happy with the results.

You need to amend the other formulas as there are some done & some not, which corrupts the results.

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

 

[dunmore83]

I'm having a really hard time with this one. Sorry!!

File below... highlighted in blue are still the differences to the closing balances of 2014-15

https://www.dropbox.com/s/49bnolcfv366ylq/2015-16-Stock-Recon-V4.xlsx?dl=0

 

[gaz_chops]

I can tell lol, some are still wrong, look at Sold, Preg & Calved for Dun, Heifers from row 83 down.

I'll take a look at the carry overs, but may need more info as to how you get your numbers.

 

[gaz_chops]

Check the source data tab, there are no dates for "Preg"?