SUM Only NEGATIVE Numbers In An Excel Range

This formula is based on  the SUMIF Function.  I have to say it is one of THE most useful functions I find in Excel.  Used to calculate conditional sums, and in the example we will look at today, we will count all of the negative values in a data range.


I have set up an example to look at the number of  refunds that Sales Reps have given in a period of one months Sales of Widget-1’s.

The formula syntax is =SUMIF(E4:E10,”<0″) and in our example below it will sum all of the negative numbers in cell range E4:E10

In the exmaple we can see that there are 22 negative values in the data set.

Let’s turn up the volume on this yes?, let’s find out how much those refunds amount in in monetary value.  We can simply amend the formula to

=SUMIF(E4:E10,”<0″,D4:D10).  This will give us the count of values in the range but return the return the sum of the corresponding values in column D.

So, we know there were 22 refunds with a value of  -550.

 Other Excel Tips

1. Search For And Highlight Invalid Data In Your Excel Worksheets

2. Perform Inexact Searches In Excel

3. Data Validation To Control User Data Entry

4. Need Excel Templates?…Look no Further



Leave a Reply

Your email address will not be published. Required fields are marked *