I have an issue with SUMIF function that drives me crazy. The thing is that looks like the Excel calculate the SUMIF till a point and not in the entire cells range (even with same length in range and sum_range).

Here's the example:

     A        B         C
1|   5   |  Type1   |   50
2|  10   |  Type2   |    
3|   5   |  Type2   |   35
4|  10   |  Type1   |
5|   5   |  Type2   |

C3 is the cell that contains the SUMIF function like this: =C1-SUMIF(B1:B5;"Type2";A1:A5) (C1 has a simple SUM function from other data) but as you can see the function doesn't work propertry. C3 shuld have the value 30 instead of 35. The value of A5 ins't sume in the SUMIF, and so on if you continue to adds rows to the table (even updating the ranges).

That's my problem here. The SUMIF function is right: range;criteria;sum_range so why isn't analyzing data in the entire A1:A5 B1:B5 range? What i'm doing wrong?

- I'm using Excel 2016

  • 1
    Are you sure the value in all the cells is "Type2"? i.e. an extra space at the end may throw off the SUMIF Apr 22, 2019 at 19:41
  • 1
    More pointedly, you're right that "C3 should have the value 30 instead of 35", but whatever's going on here, "the function doesn't work properly" is not it. When I set up the scenario as described here, the values are correct. If it's not @cybernetic.nomad 's suggestion that you have trailing spaces, there's something else you haven't told us (or noticed) that's throwing you off.
    – Alex M
    Apr 22, 2019 at 19:47
  • @cybernetic.nomad I'm 99% sure because since the first time Type2 where typed, I used Excel's autocomplete for write the others
    – FiroKun
    Apr 22, 2019 at 19:47
  • @AlexM of course the real case is a lot more complex economic data table. But, the example is almost like the real one: in the function is only involved Currency (A column) and General (B column)data types for all the cases. The other think is that C1 are involved in the operation, but C1 is just a simple SUM(D1:D5) if D column exists. This operation =C1-SUMIF(*;*;*) can be a problem?
    – FiroKun
    Apr 22, 2019 at 19:55
  • What happens if you try: =C1-SUMIF(B:B;"Type2";A:A)? Apr 22, 2019 at 20:01

1 Answer 1


The problem was isn't in the function itself, but in my interpretation of the information the formula output. Since C1 depends on the same data SUMIF use in sum_range, when I introduce new Type2 data, not only C3 was update, also C1, and since C3 output depends of C1 like =C1-SUMIF(B1:B5;"Type2";A1:A5) so C3 where updated by two ways: C1 data and SUMIF output, making I not interpretated correctly the result. I need to put more caution in this things.

  • Better upload illustration to justify how you have solved the issue ! Apr 23, 2019 at 8:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.