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

 

[Simon Lloyd]

i believe that this
=SUMIFS(Data!K2:K5000,Data!D25000,"<>40001",Data!A2:A5000,Summary!$B10
should rea
=SUMIFS(Data!K2:K5000,Data!D25000,<>"40001",Data!A2:A5000,Summary!$B10

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 (cell
formatted as text in case this makes a difference) and A2:A5000 ar
Projec
Number

The formula runs correctly and I get a sum of the amounts that have th
cos
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 projec
whether th
cost element is 40001 or not

What am I doing wrong

Thank

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[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)