SUMPRODUCT Function with Criteria for Visible Rows

[cshanahan55]

I am trying to get the totals for a column in a table based on a condition.  In my example, I want to filter by a column (Fans) and then get the count for when the value for the column (Change) is a negative number.  From what I have researched, I cannot have a criteria for this in an aggregate function, and cannot figure out how to make it work with a subtotal or sum product functions.  The key is counting the visible rows that have a negative value.

[#2 · Accepted solution]

@cshanahan55 

Here you go:

=SUMPRODUCT((Table1[Change]<0)*SUBTOTAL(102,OFFSET(E4,ROW(Table1[Change])-MIN(ROW(Table1[Change])),0)))

[#3]

@Hans Vogelaar 

Awesome formula and solution.  Thanks!