Excel tutorials

Sum of negative numbers based on the number of months in a year if the condition is a date

In this Excel tutorial I will show you how the sum of the only negative values for a particular month of the year.
For this Excel example I have chosen several ways to do the sum of negative numbers or negative values based on the number of months if we use dates as one of the conditions. Take a look at 'Image 1'.

In the column 'A' there are no sorted dates and in the 'B' column there are values (negative and positive). Our goal is to sum up all negative values (negative numbers) for a given month.

So we have two conditions

  1. A certain Month in a Year ('A' column)
  2. Negative values ('B' column)

The sum of negative values based on the month of the year can be solved using multiple formula types and in many ways. In this tutorial I will show two basic formulas, SUMIFS function and SUMPRODUCT function.

Image 1

The sum of negative values by months

How to sum negative values using the helper and Excel function SUMIFS

In this first case, I created a formula using SUMIFS function and extra 'C' column. Also in the image you see that one of the conditions is the number of months in the year, located in the 'E' column.

The 'Image 2' formula in the 'G2' cell is below. (This formula has two criteria: one criterion is the 'C' column where the Dates are located, and the second criterion is the number of months in the 'E2' cell. The sum of negative values for the fulfilled conditions is in the 'B' column. Let's say, the formula can be translated into a sentence: sum of all values less than zero (0) from the 'B' column in the same row where the number of months is equal to the number in the 'E2' cell.
=SUMIFS($B$2:$B$13;$C$2:$C$13;E2;$B$2:$B$13;"<0")

Image 2

Sum of negative values if there are two conditions in two columns

Sum of only negative values per month if we use Excel ROW function

The same result as in the previous case can be obtained using the Excel SUMIFS function and the ROW function. So, the formula in 'I2' cell is the following:
=SUMIFS($B$2:$B$13;$C$2:$C$13;ROW(A1);$B$2:$B$13;"<0")

Notice the difference between the first two formulas above (Image 2 and Image 3). In the first formula we used the argument 'E2' in the cell with the number of month '1' and in the second formula we used the ROW(A1) function which returns the number '1' as a result. By copying to the bottom ROW function will return the ordinal numbers 1, 2, 3, ..., 12, which is the replacement for our number of months per year.

Image 3

Sum of negative values based on Dates and Two columns

The sum of negative values by months, on two conditions without extra column

This case below is different from all the aforementioned. Notice on 'Image 4' that no extra column exists. And here we have two formulas. For this case I used the Excel SUMPRODUCT function. This functions as arguments used Array, which multiplies arguments so they then sum.

The first formula in 'G2' cell to 'Image 4' is as follows:
=SUMPRODUCT(($B$2:$B$15<0)*(MONTH($A$2:$A$15)=E2)*$B$2:$B$15)

Notice in the formula that I used the Excel MONTH function. This Excel function returns a number of months from a given date. Since we have set the number of months '1' in the 'E2' cell as a condition, the function will only be based on the dates that refer to the first month of the year.

EVALUATE FORMULA

If you use the Evaluate Formula or 'F9' key on a particular argument SUMPRODUCT function, we can observe the following:

=SUMPRODUCT(({4;6;11;-5;-7;-15;-2;5;20;25;-50;-101;0;0}<0)*({1;1;1;1;1;12;2;3;1;2;6;6;1;1})=1)*{4;6;11;-5;-7;-15;-2;5;20;25;-50;-101;0;0})

If your previous formula is unclear, maybe the other is clearer.
=SUMPRODUCT({0;0;0;1;1;0;0;0;0;0;0;0;0;0}*$B$2:$B$15)
or
=SUMPRODUCT({0;0;0;1;1;0;0;0;0;0;0;0;0;0}*{4;6;11;-5;-7;-15;-2;5;20;25;-50;-101;0;0})

So for every number '1' in the curly brackets formula will sum up the values that are in the same row in the 'B' column.

Or perhaps this formula into which I added two minuses in front (two dashes). In this formula, at the end of numbers 4 and 5, indicate the number of rows in the column.
=SUMPRODUCT(--($B$2:$B$15<0)*(MONTH($A$2:$A$15)=1)*$B$2:$B$15)

or evaluate formula

=SUMPRODUCT(({FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE})*(MONTH($A$2:$A$15)=E2)*{"Value Example";4;5})

or this step evaluate formula

=SUMPRODUCT({0;0;0;1;1;1;1;0;0;0;1;1;0;0}*(MONTH($A$2:$A$15)=1)*{"Value Example";4;5})

So, notice 'Image 4' in the fourth and fifth row in the 'B' column, find the numbers '-5' i '-7'
The final calculation would have been =>>>> (1*(-5))+(1*(-7))=-12

Image 4

Sum of negative values for two conditions

How to sum all the negative values for a given month without any auxiliary columns

This last example is similar to the previous but again to avoid using a particular cell as a condition for the number of months, we can use the ROW function.

So the formula in 'I2' cell is the following:
=SUMPRODUCT(($B$2:$B$15<0)*(MONTH($A$2:$A$15)=ROW(A1))*$B$2:$B$15)

Image 5

Sum of negative values for a given month without using auxiliary columns

Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.