# Formula change with Validation

#### srizki

##### Well-known Member
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
MNOP
2292,0052,005
230149,545,547LAND155,723,593
23157,861,646WIP69,143,549
232882,513,761BUILDING926,202,813
233593,168,260EQUIPMENT648,423,537
234111,293LEASEHOLDIMP111,293
2353,650,811LANDIMPROVEMENT3,723,778
2361,686,851,3181,803,328,563
SEPTEMBER 2006

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### RalphA

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member
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.

Replies
3
Views
115
Replies
5
Views
58
Replies
3
Views
102
Replies
4
Views
36
Replies
2
Views
78