Formula change with Validation

[srizki]

Please check the spreadsheet, in the formula =SUMIF($N$11:$N$228,"WIP",$C$11:$C$228), $C$11:$C$228 refers to 2005 and in the formula
=SUMIF($N$11:$N$228,"WIP",$O$11:$O$228) O refers to 2005, I want the formula to work like this, if I change 2005 to 2005 vice versa by validation, I want the formula to change accordingly.
I tried to make this formula, but of course there is something wrong that I dont know what.
=SUMIF(M229="2005",($N$11:$N$228,"TLAND",$C$11:$C$228),($N$11:$N$228,"TLAND",$O$11:$O$228))

Thanks

PP&E Rollforward_All Corps.xls
	M	N	O	P
229	2,005		2,005
230	149,545,547	LAND	155,723,593
231	57,861,646	WIP	69,143,549
232	882,513,761	BUILDING	926,202,813
233	593,168,260	EQUIPMENT	648,423,537
234	111,293	LEASEHOLDIMP	111,293
235	3,650,811	LANDIMPROVEMENT	3,723,778
236	1,686,851,318		1,803,328,563
SEPTEMBER 2006

 

[RalphA]

Try this"
=IF(M229="2005",SUMIF($N$11:$N$228,"TLAND",$C$11:$C$228),SUMIF(N$11:$N$228,"TLAND",$O$11:$O$228))

However, be aware that "2005" is NOT the same as "2,005".

 

[srizki]

RalphA,
Thanks
It worked good, I did not know, 2,005 is different from 2005, although using =isnumber() or =istext(), gives same result.

thanks Ralph

 

[srizki]

RalphA,
Thanks
It worked good, I did not know, 2,005 is different from 2005, although using =isnumber() or =istext(), gives same result.

thanks Ralph

 

[RalphA]

Just remember that text, such as "2,005" means EXACTLY a 2, followed by a comma, folloed by two zeros, followed by a 5, and "2005" means EXACTLY a 2, followed by two zeros, followed by a 5. As you can understand, the text, "2,005" <> "2005". However, the NUMBERs 2,oo5 and 2005 are identical. To Excel, as to us humans, 2000 is identical to 2,005. But, as your English teacher would tell you, "English" is NOT the same as "english". And, to Excel, "2005" is NOT the same as "2005". Have I made the above clear enough? Two texts must be IDENTICAL to be "the same"; anything else, even a comma, or a period, or an interior space in only one of the two, makes them DIFFERENT! To a computer, since it goes by absolutely STRICT logic, just one character different i two texts is no different than you calling a guy "Zachery Taylor", when his name is "Abraham Lincoln", and expecting Old Abe to undestand that you are calling him.