CODEWITHSUNDEEP
excelsumifs
user1093111
user1093111

Reputation: 1111

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")

Upvotes: 0

Views: 1779

Answers (2)

ashleedawg
ashleedawg

Reputation: 21619

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:

Upvotes: 2

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

That's not how SUMIFS works. Try this:

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

Upvotes: 1

Related Questions