Search code examples
excelsumifs

SUMIF with multiple criteria failing with <> operator

I have a dataset like so:

00089   15498   
00789   15498   
10521   15498   
10854   15498   
11058   15498   
11992   15498   
15498   15498   1099755
15499   15498   14321.25
15501   15498   74171.25
15503   15498   4931.25
15505   15498   255521.25
15508   15498   1425

I want to exclude certain numbers in column A and sum column C. When I use a <> operator it sums all of column C. My formula:

=SUMIFS($C$3:$C$14,$A$3:$A$14,{"<>15498","<>15499"},$B$3:$B$14,"15498")
Solution

  • You've mixed up the syntax. Try:

    =SUMIFS($C:$C,$A$A,"<>15498",$A$A,"<>15498")

    It's a good idea to check the documentation for any functions your using that you're not 100% sure on. Here's the a link to the documentation for SUMIFS.

    An excerpt:

    Syntax: SUMIFS

    SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)

    • Sum_range (required) - The range of cells to sum.

    • Criteria_range1 (required) - The range that is tested using Criteria1.

    • Criteria1 (required) - The criteria that defines which cells in Criteria_range1 will be added. For example, criteria can be entered as 32, >32, B4, "apples", or "32".

      • Criteria_range2, criteria2,
      • Criteria_range3, criteria3,
      • Criteria_range4, criteria4, etc…… (optional)
        Additional ranges and their associated criteria. (Up to 127 range/criteria pairs.)

    (Source)

    For example, if you wanted to sum all of Column A from rows where Column B is more than 10 and Column C contains the string "Yes", then you would use: 

    =SUMIFS($A:$A,$B$B,">10",$C$C,"Yes")

    Google Tip:

    There is a ton of information online - sometimes too much, and in some cases outdated information or poorly written examples. I find it helpful to be be specific on my searches.

    For example, when I want to find documentation for an Excel function - which is lots (even after doing this for a couple decades) - I search a specific way in an attempt to filter out the, well, crap...

    • for Excel Worksheet Functions:
      FunctionNameexcel site:office.com like sumifs excel site:office.com

    • for Excel VBA Functions/Methods/etc:
      FunctionNameexcel vba site:msdn.com

    More Information: