CODEWITHSUNDEEP
excel
user3961155
user3961155

Reputation:

How do I total an column of number conditionally in Excel

In excel, I have a column of numbers such that if the number is positive, the text color is black, if the number is negative, the number is red. How can I, in a fresh cell, total only the red numbers in the column?

Upvotes: 0

Views: 183

Answers (2)

Brino
Brino

Reputation: 2502

Here is my example.

In this example I have a range of random integers between -100 and 100. This range is Sheet1!$B$3:$B$26.

I have applied conditional formatting such that:

  • positive numbers have black text,
  • negative numbers have red text.

To do this, just highlight your range, set a conditional format using a custom formula, and set the format using the first cell in your range.

  • custom formula for positive cells is =B3>=0.
  • custom formula for positive cells is =B3<0.

I have created two cells which contain the sum of the table using the sumif() function:

  • Sum of Positive Numbers: =sumif(your_range, ">=0")
  • Sum of Negative Numbers: =sumif(your_range, "<0")

Note: that in your case, you said you want to sum the cells with red text, all of which are negative integers, so all I really need to do is sum the cells if they are a negative integer.
To truly sum cells based on a format such as text color, you could write a vba function/macro and iterate through the cells in the range.

Upvotes: 0

shruti1810
shruti1810

Reputation: 4037

You can use: =SUMIF(A1:A10,"<0")

Upvotes: 3

Related Questions