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]

That works. Great!

A couple for changes required to the file... are you on Elance? I'm happy to transfer you a little cash for your time if you would like to get some compensation for your time?! It's a big help.

The formulas are working fine until I get to the group in Row 58. It doesn't seem to be picking up the numbers. What am I missing here?

Also I would like a lookup formula for the birth dates in column J in the Deaths, Livestock-Sales & Given-Away worksheets. However as I vlookup only works to the right I can't use it for this one i.e. column T will be the lookup column & column K will be the column to retrieve the date from in the source data worksheet

https://www.dropbox.com/s/mu7nb4vv97rfmwg/Gaz-17-Jul-15-4-23pm.xlsx?dl=0

 

[gaz_chops]

A thank you is suffice.................. Ive just downloaded file, I'll take a look later.

 

[gaz_chops]

Fixed the formula, it still had the reference to the Births that you removed, so resulted in a #N/A error.

Birth Dates are in col J on all 3 tabs, if there is no date, it will return "No Date".

https://www.dropbox.com/s/dpxdzgolfecpaok/Gaz-17-Jul-15-4-23pm.xlsx?dl=0

 

[dunmore83]

Ok no worries. Thank you!

Can I get that to work when copied across into AB61, AE61, AN61, AQ61 etc... so it would return the total births, deaths etc for the entire period prior to Jul-14 & inside the range P1 & P2 ?

 

[gaz_chops]

Check that this is what you want?

https://www.dropbox.com/s/dpxdzgolfecpaok/Gaz-17-Jul-15-4-23pm.xlsx?dl=0

 

[gaz_chops]

I added the changes to the other rows as well.

 

[dunmore83]

Thanks.

Getting close. However the formula in the deaths column appears to reference column W on the source data sheet. I would like it to reference column AI in the source data sheet.

I will need a formula in DE61 & DE62 to reference column W for Still Births. note: I will be changing all occurrences of "Deaths" to "Still Born" in column W of the source data sheet.

Cheers

 

[gaz_chops]

I've adjusted it to look at column AI, check a few to make sure it looks correct.
The formula in DE looks ok!

https://www.dropbox.com/s/dpxdzgolfecpaok/Gaz-17-Jul-15-4-23pm.xlsx?dl=0

 

[dunmore83]

=IF(V128="E","E",IF(AO128="no ai",((S128-AL128)+283),IF(AO128<>AR128,(AF128+283),0)))

 

[dunmore83]

Hi Gaz,

This is probably quite simple but it's got me tricked!

AR129 has the value 14 in the cell. I need this part of the formula AO129<>AR129 to accept the if function if the values in AO129 are equal to or less than +14 or equal to or less than -14.

How would I do this?

Thanks