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

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

where K2:K5000 is the value column, D2:D5000 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!D2:D5000,"<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

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!D2:D5000,"<40001*",Dat a!A2:A5000,Summary!$B10)


--
Biff
Microsoft Excel MVP


"Chesil" wrote in message
...
I'm having a problem with a sumifs function. When I use this formula:

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

where K2:K5000 is the value column, D2:D5000 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!D2:D5000,"<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