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]

Carry-over formulas

Group #1 heifers as an example;

1st qualification = birth dates
2nd qualification = owner
3rd qualification = sex
4th qualification = count anything that qualifies from the above in which the date of the death, preg, calving, sold, given away etc. is prior to the start of the financial year i.e. prior to 1-July-2014 or 01-July-15

e.g. birth date 05-Mar-15, dun, heifer, died on 21-Mar-15 then is would be counted as -1 (for the 2015-16 version) as it qualifies for group #1 & the death was prior to the start of the financial year

OK thats what I thought, update the file as above then upload and I can adjust the formulas.

 

[dunmore83]

I'm not sure what happened there as I did update them but the changes hadn't been saved.

Should be all updated now but nothing showing still..??!

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

 

[gaz_chops]

Sorry but there not done, some are! They should be the same formulas as #1

Not worth me changing the Carry-over till all correct.

 

[dunmore83]

=countifs('Cattle-Sales'!J10:J18,"Heifer",SUMPRODUCT(--(MONTH('Cattle-Sales'!$D$9:$D$19)=MONTH($M$2)),--(YEAR('Cattle-Sales'!$D$9:$D$19)=YEAR($M$2)),'Cattle-Sales'!$P$9:$P$19))

This is not working... is it close or am I way off?!!

This part works;

=SUMPRODUCT(--(MONTH('Cattle-Sales'!$D$9:$D$19)=MONTH($M$2)),--(YEAR('Cattle-Sales'!$D$9:$D$19)=YEAR($M$2)),'Cattle-Sales'!$P$9:$P$19))

Just need to add this to it

countifs('Cattle-Sales'!J10:J18,"Heifer"

 

[dunmore83]

I can't see which ones on lines 19, 40, 61, 82, 103 & 145 are not updated?

Note that I am not updating the Sier formulas... only concerned with Dun. Will adjust the Sier when working correctly

 

[gaz_chops]

=countifs('Cattle-Sales'!J10:J18,"Heifer",SUMPRODUCT(--(MONTH('Cattle-Sales'!$D$9:$D$19)=MONTH($M$2)),--(YEAR('Cattle-Sales'!$D$9:$D$19)=YEAR($M$2)),'Cattle-Sales'!$P$9:$P$19))

This is not working... is it close or am I way off?!!

This part works;

=SUMPRODUCT(--(MONTH('Cattle-Sales'!$D$9:$D$19)=MONTH($M$2)),--(YEAR('Cattle-Sales'!$D$9:$D$19)=YEAR($M$2)),'Cattle-Sales'!$P$9:$P$19))

Just need to add this to it

countifs('Cattle-Sales'!J10:J18,"Heifer"

Is this to do with the above?

 

[gaz_chops]

I can't see which ones on lines 19, 40, 61, 82, 103 & 145 are not updated?

Note that I am not updating the Sier formulas... only concerned with Dun. Will adjust the Sier when working correctly

It's NOT just the carry over formulas! You need to adjust row 20 then copy down to row 31, then row 41 etc.

 

[dunmore83]

No something different in my workbook

 

[dunmore83]

Ok.

Just making sure that the formula that goes in the carry-over line should remain different to those on the month lines?

 

[gaz_chops]

Ok. Just making sure that the formula that goes in the carry-over line should remain different to those on the month lines?

Yes it is different.