• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Subtotals in Filtered data filtering out the positive numbers

jcalvacca

Member
Hello forum, thanks in advance for the help.


I am using a table with different dollar amounts, both negative and positive in the same column. I understand that I can use the SUBTOTAL function to derive the sum of the numbers, however I would like to filter just the NEGATIVE numbers and make a summation of those numbers.


Ex:


15

12

13

16

-8

-22

65


Should return $30 (would like it to return a positive number if possible)

Remember, this data will be coming from filtered table out of a table.


Thanks again.
 

Hui

Excel Ninja
Staff member
jcalvacca


I'm a bit confused here


You can't use the Subtotal function on Tables of data.


You can use a normal Sumif function like =-SUMIF(B2:B8,"<0")
 

jcalvacca

Member
Not a table, its a list. Sorry about that.


Can =-SUMIF(B2:B8,"<0") this be used with the subtotal function on filtered data? This would be perfect if it was a true range, but with the list it "appears" as a true range but the cells are are not.
 

jcalvacca

Member
Ok let me show an example...


Jerry 65

Mike 17

Bill 22

Jerry 46

Mike -9

Jim 26

Mike -3

Jerry -10


If I want "Jerry" and sum of the negative numbers, this should return "10"

If I want "Jerry" and sum of all the positive numbers, this should return 111


If i want "Mike" and sum of all the negative numbers, this should return "12"

If i want "mike" and sum of all the positive numbers, this should return 17


If I want "Bill" and sum off all the negative numbers, this should return "0"


Data is in a list.
 

Hui

Excel Ninja
Staff member
Assuming your data is in A2:B9

Then in order of your example above


=SUMIFS(B2:B9,A2:A9,"Jerry",B2:B9,"<0")

=SUMIFS(B2:B9,A2:A9,"Jerry",B2:B9,">0")


=SUMIFS(B2:B9,A2:A9,"Mike",B2:B9,"<0")

=SUMIFS(B2:B9,A2:A9,"Mike",B2:B9,">0")


=SUMIFS(B2:B9,A2:A9,"Bill",B2:B9,"<0")
 
Top