Not Equal To not working in SUMIFS function

[lracrft]

I have created a SUMIFS function that is returning an incorrect value and I have narrowed the cause down to the not equal to (<>) not working properly. My equation looks like this:

=SUMIFS(Sheet2!F:F,Sheet2!O:O,741,Sheet2!Q:Q,251,Sheet2!T:T,1,Sheet2!W:W,"<>12017")

The value it returns for me is the value that it would return if the "Sheet2!W:W,"<>12017"" argument were not there. I have checked the format of the cells of both where the formula is located as well as the format of the cells where the data is pulling from - they match and the problem isn't changed by changing the format of the cells. the "12017" value is a concatenation of two other fields, so I also tried pasting the solution to that formula as values but that didn't work either. What else could possibly be causing excel to just ignore my <> argument?

 

[spydey]

Hey there, I could be 100% wrong here but it seems to me that you don't have a criteria for your first criteria range.

You have:

=SUMIFS(Sheet2!F:F, NO CRITERIA HERE, Sheet2!O:O ...............

I am assuming that Sheet!2O:O is actually your second criteria range, and thus you have no criteria for your 1st criteria. However, like I said, I could be 100% wrong in my assumption.

-Spydey

 

[RoryA]

Welcome to the forum.

If the last criterion isn't having any effect, then none of the cells are actually 12017. Perhaps there are leading or trailing spaces?

 

[lracrft]

Actually because this is a SUMIFS rather than a SUMIF, the Sheet2!F:F is the sum range, not a criteria range. The SUMIF function puts the sum range last, but for SUMIFS it is first. Is that what you're thinking?

 

[steve the fish]

Make your concatenate formula like this for example:

=CONCATENATE(120,17)+0

Should work then. Excel is seeing your number as text.

 

[lracrft]

RoryA - I did try the trim function on Sheet 2 to mitigate that potential issue - I also tried entering a leading 0 just in case that could be causing an issue. Neither of those things had an effect.

 

[RoryA]

If you check one of the cells you think contains 12017 using LEN() what does it return?

 

[lracrft]

steve the fish - THANK YOU! That worked!!!

 

[spydey]

Actually because this is a SUMIFS rather than a SUMIF, the Sheet2!F:F is the sum range, not a criteria range. The SUMIF function puts the sum range last, but for SUMIFS it is first. Is that what you're thinking?

You are 100% correct, sir. I was thinking of countifs for some reason, which has all the criteria ranges proceed the needed criteria. Sorry about the confusion.

-Spydey