the following sumif does not work

=SUMIF($D$10:$D$17,"Cyear",$E$10:$E$17)*-1

A B C D E
Asset Month Day Year (of purchase) Cost


A - D are formated as general
E is formated as ","
B, C, D also use Validation and pick from a list.
Cyear = the current year

The sumif is located in E at the end of the list.


I have a list of assets purchased frmo 1995 thru 2005. At the bottom of
E I have the total cost. Below that i have the sumif formula. i want
it to total all the assets that have a purchase year greater than the
Cyear.

*** Sent via Developersdex http://www.developersdex.com ***

Just a minor change
"Cyear" "" & Cyear

the first treats it as Text and the second treats it as a Value
--
steveB

Remove "AYN" from email to respond
"Tim Russell" wrote in message
...
the following sumif does not work

=SUMIF($D$10:$D$17,"Cyear",$E$10:$E$17)*-1

A B C D E
Asset Month Day Year (of purchase) Cost


A - D are formated as general
E is formated as ","
B, C, D also use Validation and pick from a list.
Cyear = the current year

The sumif is located in E at the end of the list.


I have a list of assets purchased frmo 1995 thru 2005. At the bottom of
E I have the total cost. Below that i have the sumif formula. i want
it to total all the assets that have a purchase year greater than the
Cyear.

*** Sent via Developersdex http://www.developersdex.com ***

Hi Tim

Try this

=Sumif($D$10:$D$17,""&Cyear,$E$10:$E$17)*-1

hth

J

"Tim Russell" wrote:

the following sumif does not work

=SUMIF($D$10:$D$17,"Cyear",$E$10:$E$17)*-1

A B C D E
Asset Month Day Year (of purchase) Cost


A - D are formated as general
E is formated as ","
B, C, D also use Validation and pick from a list.
Cyear = the current year

The sumif is located in E at the end of the list.


I have a list of assets purchased frmo 1995 thru 2005. At the bottom of
E I have the total cost. Below that i have the sumif formula. i want
it to total all the assets that have a purchase year greater than the
Cyear.

*** Sent via Developersdex http://www.developersdex.com ***

right now you are checking against the string literal "Cyear".

=SUMIF($D$10:$D$17,"2005",$E$10:$E$17)*-1

or if the year is entered in a cell

=SUMIF($D$10:$D$17,"" & cYear,$E$10:$E$17)*-1

where cYear is a single cell Named range.

--
Regards,
Tom Ogilvy


"Tim Russell" wrote in message
...
the following sumif does not work

=SUMIF($D$10:$D$17,"Cyear",$E$10:$E$17)*-1

A B C D E
Asset Month Day Year (of purchase) Cost


A - D are formated as general
E is formated as ","
B, C, D also use Validation and pick from a list.
Cyear = the current year

The sumif is located in E at the end of the list.


I have a list of assets purchased frmo 1995 thru 2005. At the bottom of
E I have the total cost. Below that i have the sumif formula. i want
it to total all the assets that have a purchase year greater than the
Cyear.

*** Sent via Developersdex http://www.developersdex.com ***

I don't understand what your are saying - "Cyear" "" & Cyear.



*** Sent via Developersdex http://www.developersdex.com ***

I figured out what your were saying - ""&CYear.

Thank you for the help!


*** Sent via Developersdex http://www.developersdex.com ***

Why multiply by -1, why not just negate it.

=-SUMIF($D$10:$D$17,""&cYear,$E$10:$E$17)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tim Russell" wrote in message
...
the following sumif does not work

=SUMIF($D$10:$D$17,"Cyear",$E$10:$E$17)*-1

A B C D E
Asset Month Day Year (of purchase) Cost


A - D are formated as general
E is formated as ","
B, C, D also use Validation and pick from a list.
Cyear = the current year

The sumif is located in E at the end of the list.


I have a list of assets purchased frmo 1995 thru 2005. At the bottom of
E I have the total cost. Below that i have the sumif formula. i want
it to total all the assets that have a purchase year greater than the
Cyear.

*** Sent via Developersdex http://www.developersdex.com ***