5

I am trying to use SUMIFS to sum a couple of conditions. I want my sum range to be column A and my first criteria range is column B, the criteria is that column B has something in it or essentially it isn't blank/0. The next criteria is if column C matches the year which is simple and I can get that to work, but my problem arises from the criteria of the first test. I have tried:

  1. SUMIFS(column A, column B, column B > 0, column C, "16")
  2. SUMIFS(column A, column B, column B <> 0, column C, "16")
  3. SUM(SUMIFS(column A, column B, "1", column C, "16"), SUMIFS(column A, column B, "2", column C, "16"), SUMIFS(column A, column B, "3", column C, "16")...

Obviously, I do not want to use option 3 but it did seem to give me the right result. If I knew that the number in column B would be always under 5 then I may use this but as of now, I have to assume the number in column B can be from 0-1000. Is there something I am missing here?
All I want to do is sum up column A if column B is not blank or 0. Thanks.

Ivan Aracki
  • 4,861
  • 11
  • 59
  • 73
ryan
  • 79
  • 1
  • 2
  • 8

3 Answers3

7

Better answer found at ExcelJet

Use only "<>" as criteria, e.g.

=SUMIF(C5:C11,"<>",D5:D11)

This allows rows in criteria column to be included if their value is zero, thus excluding ONLY blanks in the criteria column.

Renier
  • 480
  • 5
  • 13
1

You can do SUMIFS() with comparison as criteria by enclosing your criteria in quotation marks:

=SUMIFS(A:A,B:B,">0")

Note that >0 criteria also works for blank cells as Excel evaluates them to zero.

However, if you do it this way:

=SUMIFS(A:A,B:B,"<>0")

blank cells will pass the criteria, only cells containing 0 value will be skipped.

ttaaoossuuuu
  • 7,786
  • 3
  • 28
  • 58
  • Wow... So I just need to surround my criteria with quotation marks? For the first attempt would the correct syntax be: "column B > 0" or column B ">0"? BTW when I say column A/B/C I mean the reference so it would be 'sheet 1'!$A:$A but I imagine you understood that. Thanks for the help! – ryan Jun 15 '16 at 13:56
  • The correct syntax in your notation would be `=SUMIFS('sheet 1'!$A:$A,'sheet 1'!$B:$B,">0",...,...)` – ttaaoossuuuu Jun 15 '16 at 13:58
  • ^ Thanks, I should have commented after I tried it on excel :P. Helpful, correct response, thank you very much! If you wouldn't mind I also have another outstanding excel question that is a bit tricky -> [link](http://stackoverflow.com/questions/37792044/if-stmt-false-eval-next-row) – ryan Jun 15 '16 at 14:00
-1

I am not getting correct result with "<>". Column A contains numbers. Column B has formulas which gives an amount or a blank. I used the formula '=SUMIF(B:B,"<>",A:A)'. This formula adds up all numbers in Column A, even if corresponding cells in Column B are blank (based on formula)

Bipin Shah
  • 1
  • 1
  • Welcome Bipin. Please **edit** your answer to correct the formula, rather than adding a comment. – Joman68 Dec 20 '20 at 00:02
  • Bipin, try "< >" with a space between the chevrons. Typing "<>" without the space causes the error you are getting. – vbaJones Jan 10 '22 at 16:29