Using Excel, how does one sum the values in all the cells of a group of cells in rows and columns which have values greater then zero (positive) into a total sum of the positive values, and separately sum the cells in the same group which have values less than zero (negative) into a total of the negative values to obtain separately the resulting positive and negative sums displayed in two separate cells?

Does one write the formula in CountIf or some other formula type?

  • 1
    If you want sums, use SUMIF. It will handle a range that is a block of rows and columns. Use one in one cell for positive values and one in another cell for negative values. They will be the same formula except one will use > and one will use <. Give it a try and if you run into problems, post back with the problem (sample data, your formula, and what result you're getting).
    – fixer1234
    Commented Aug 15, 2019 at 1:58
  • You need to apply conditional SUM for +ve & -ve numbers,, better Edit your post and add some sample data, will help us to suggest the best one to you. Commented Aug 15, 2019 at 5:03
  • To add to @fixer1234 his comment, the comparison operator < and > needs to be wrapped into double quotes. So for example, in range A1:B5 with both negative and positive values, the SUMIF would look like: =SUMIF(A1:B6,">0") for positive values, and =SUMIF(A1:B6,"<0") for negative.
    – JvdV
    Commented Aug 15, 2019 at 6:50
  • 1
    @JvdV,, better post all these as an answer instead of comment, help OP and others too ☺ Commented Aug 15, 2019 at 7:43

1 Answer 1


Some mock-up sample data in range A1:B5:

enter image description here

Formula in E2:


Formula in E3:


Notice how the comparison operator is wrapped in double quotes?

  • Many Thanks and Kindest Regards for your precise answers.
    – Sapphire
    Commented Aug 15, 2019 at 17:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .