sumifs problem

C

Chesil

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
 
S

Simon Lloyd

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

Chesil;535107 said:
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 (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!D2:D5000,"<>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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top