sumifs problem

[Chesil]

I'm having a problem with a sumifs function. When I use this formula:

=SUMIFS(Data!K2:K5000,Data!D25000,"=40001",Data!A2:A5000,Summary!$B10)

where K2:K5000 is the value column, D25000 are cost elements (cells
formatted as text in case this makes a difference) and A2:A5000 are Project
Numbers

The formula runs correctly and I get a sum of the amounts that have the cost
element of 40001 for the project number referenced in B10.

When I change this formula to:

=SUMIFS(Data!K2:K5000,Data!D25000,"<>40001",Data!A2:A5000,Summary!$B10)

I get a total of all cost elements within the referenced project whether the
cost element is 40001 or not.

What am I doing wrong?

Thanks

 

[T. Valko]

SUMIFS and COUNTIFS (like SUMIF and COUNTIF) evaluate TEXT numbers and
NUMERIC numbers as being equal. So, you have to force the criteria to be
evaluated as TEXT. Try this:

=SUMIFS(Data!K2:K5000,Data!D25000,"<>40001*",Data!A2:A5000,Summary!$B10)