counting filtered values

[Lancasterbe]

Hi Community
I was wondering if anyone can help me with the following
I have a sheet that contains about 20 columns, when I filter on one column I want to sum and Average that column which I can do with the aggregrate function
The thing I cant do is -1. Count values over zero and 2. Count the values less than zero (on the filtered values only)
Any help would be appreciated
Cheers
Brian

[Andy Pope]

One way would be to use a helper column with a SUBTOTAL formula using count.
This would then contain a 1 or 0 depending on whether hidden or not.

You can then use a SUMPRODUCT formula to test column to be summed or averaged has a positive or negative value, multiple that by value and helper column value.

Small example attached

[Lancasterbe]

Thank you for your reply Andy (and demo sheet!)
Is there a formula to count the values over zero, and the values under zero?
In your example the answer would be 2 above zero and 2 below zero
Cheers
Brian

[Andy Pope]

Yes, just modify the formula so only the helper column is counted
So modified formula for less than zero would be

=SUMPRODUCT((D8:D16<0)*(E8:E16))