# 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,"&#60;0")

#### jcalvacca

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

Can =-SUMIF(B2:B8,"&#60;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,"&#60;0")

=SUMIFS(B2:B9,A2:A9,"Jerry",B2:B9,"&#62;0")

=SUMIFS(B2:B9,A2:A9,"Mike",B2:B9,"&#60;0")

=SUMIFS(B2:B9,A2:A9,"Mike",B2:B9,"&#62;0")

=SUMIFS(B2:B9,A2:A9,"Bill",B2:B9,"&#60;0")

#### jcalvacca

##### Member
Thanks.

However Im running Excel 2003. So now I think I'm back on the SUBTOTAL function?

#### Hui

##### Excel Ninja
Staff member
=Sumproduct(1*(A2:A9="Jerry")*(B2:B9&#60;0),(B2:B9))

etc