SumIfs with a NOT

[jerwood]

I am using SumIfs, and it is working quite well, except that I need to throw in a NOT [TextValue] as a condition.


So, my formula looks like this:

=SUMIFS($D$36:$D$521,$B$36:$B$521,"="&C4,$A$36:$A$521,"<>651",$A$36:$A$521,"<>2",$A$36:$A$521,"<>5",$B$36:$B$521,"<>"&C8)


From what I can tell the criteria with a specific number, like "<>651" and "<>2" are working fine. The last one should be NOT "Other." When I use "<>Other" or "<>"&"Other" or "<>"&C8 (contains "Other"), it doesn't return any results.


Here's What I'm trying to accomplish:

D column = text in C4

AND A column <> 651

AND A column <> 2

AND A column <> 5

AND B Column <> Other


Is it breaking because I'm trying to use <> on text?


Please help!


Jerwood

 

[sumsul_buet]

Plz Check :


https://docs.google.com/spreadsheet/ccc?key=0AlMNziTCmrQIdEhyQUJIODZoTTRMRVBWbk1LWHpMTWc


Hope this will help, if i get your problem correctly

 

[Luke M]

@jerwood,


The formula you gave works correctly in my workbook. Are there any extra spaces either in C8, or in your data, that could be messing something up?