Not Equal To (<>) not working in SUMIFS

[minus4]

Hello all,

I can't get Not Equal To <> syntax to work in my SUMIFS formula. SUMIFS is returning value as if Not Equal To criteria does not exist.
Data tab is queried data table that contains text.

=SUMIFS(Data[Length],Data[ItemID],"7610001006",Data[Status],"Sold",Data[Year],"2019",Data[WhseID],"<>"&"85")

I tried the alternatives below and it did not work either.
=SUMIFS(Data[Length],Data[ItemID],"7610001006",Data[Status],"Sold",Data[Year],"2019",Data[WhseID],"<>85")

Any help is greatly appreciated.

 

[sandy666]

check if the numbers are numbers in your source range
then try all numbers without "", even year

 

[minus4]

check if the numbers are numbers in your source range
then try all numbers without "", even year

OK. So numbers are actually numbers, so even without "" SUMFIS returns correct values. But Not Equal To still doesn't work.

 

[sandy666]

maybe try: Data[WhseID],"<>"&A1) where A1=85

and check if the syntax is correct

SUMIFS function - Microsoft Support

How to use the SUMIFS function in Excel, one of Excel’s math and trig functions, to add arguments that meet multiple criteria.

support.microsoft.com

 

[minus4]

maybe try: Data[WhseID],"<>"&A1) where A1=85

and check if the syntax is correct

SUMIFS function - Microsoft Support

How to use the SUMIFS function in Excel, one of Excel’s math and trig functions, to add arguments that meet multiple criteria.

support.microsoft.com

OK. So A1=85. I converted A1 to text. Not Equal To still does nothing. Converted it back to number, and it's the same.
Formula still returns value as if Not Equal To is not there.

If I do the (sum of all whse ID)-(sum where whse ID=85), then I get the number I want.
=SUMIFS(Data[Length],Data[ItemID],"7610001006",Data[Status],"Sold",Data[Year],"2019")-SUMIFS(Data[Length],Data[ItemID],"7610001006",Data[Status],"Sold",Data[Year],"2019",Data[WhseID],"85")

SUMIFS works correctly, only Not Equal To is not. So I don't think Syntax is an issue.

 

[sandy666]

post representative example using XL2BB
or
post a link to the shared Excel file with representative example and expected result via onedrive, googledrive, dropbox or any similar service

I suggest second option

 

[CA_Punit]

can you post a sample of your table column no. Data[WhseID]

 

[joeu2004]

=SUMIFS(Data[Length],Data[ItemID],"7610001006",Data[Status],"Sold",Data[Year],"2019",Data[WhseID],"<>"&"85")
[....]
=SUMIFS(Data[Length],Data[ItemID],"7610001006",Data[Status],"Sold",Data[Year],"2019",Data[WhseID],"<>85")

Note that there is no difference between the two. Both pass the string "<>85" to SUMIFS. It makes no difference how the string is formed (concatenation in the first case).

However, I wonder if you inadvertently corrected the problem when you typed(?) the formula into your posting.

Be sure to copy-and-paste the formula from the Formula Bar.

In the Formula Bar, I wonder if I you have a space before "<>" or a space in between.

To demonstrate, enter 1 into A1 (verify ISNUMBER(A1) returns TRUE), then copy-and-paste the following formulas into Excel.

=COUNTIF(A1," <>2")
=COUNTIF(A1,"< >2")
=COUNTIF(A1,"<> 2")
=COUNTIF(A1,"<>2")

The first two formulas return zero because of the errant spaces. The count the cells with the text " <>2" and "< >2", not the cells that do not equal 2.

The last two formulas return one, as expected.