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
    Aug 15 '19 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. Aug 15 '19 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
    Aug 15 '19 at 6:50
  • 1
    @JvdV,, better post all these as an answer instead of comment, help OP and others too ☺ Aug 15 '19 at 7:43

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
    Aug 15 '19 at 17:29

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.