CODEWITHSUNDEEP
excel
333Matt
333Matt

Reputation: 1188

Why aren't negative values properly summed in this Excel equation?

I have an excel spreadsheet that is using some overcomplicated looking formula to calculate a total for a column of payment amounts. Only problem is, negative amounts are not being subtracted from the total. They're being ignored or treated as zeroes. Yes, I have tried taking the "> 0" off the end of the equation, but it results in an error and a display of value! in the cell.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(E5, ROW(E5:E26)-ROW(E5),,1)),--(E5:E26 > 0))

Upvotes: 0

Views: 361

Answers (2)

yesennes
yesennes

Reputation: 1277

The reason it ignores negative values is that (E5:26 > 0) gives you a array of true/false, false when the value is negative, then the "--" changes them to corresponding 1s and 0s. The sumproduct then multiplies the corresponding values, and the negatives get multiplied by the 0s and don't matter.

If you wanted to just sum positive values, just use the =SUM(E5:E26), though you may want to confirm that is what is required of the spreadsheet because it appears that the creator intentionally made it ignore negatives.

Upvotes: 1

Abe Gold
Abe Gold

Reputation: 2357

Remove all this:

--(E5:E26 > 0)

Although it would seem the only reason SUMPRODUCT was used was to skip negaive numbers...

Upvotes: 1

Related Questions