SOLVED

SUMPRODUCT Function with Criteria for Visible Rows

%3CLINGO-SUB%20id%3D%22lingo-sub-1818738%22%20slang%3D%22en-US%22%3ESUMPRODUCT%20Function%20with%20Criteria%20for%20Visible%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1818738%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20get%20the%20totals%20for%20a%20column%20in%20a%20table%20based%20on%20a%20condition.%20%26nbsp%3BIn%20my%20example%2C%20I%20want%20to%20filter%20by%20a%20column%20(Fans)%20and%20then%20get%20the%20count%20for%20when%20the%20value%20for%20the%20column%20(Change)%20is%20a%20negative%20number.%20%26nbsp%3BFrom%20what%20I%20have%20researched%2C%20I%20cannot%20have%20a%20criteria%20for%20this%20in%20an%20aggregate%20function%2C%20and%20cannot%20figure%20out%20how%20to%20make%20it%20work%20with%20a%20subtotal%20or%20sum%20product%20functions.%20%26nbsp%3BThe%20key%20is%20counting%20the%20visible%20rows%20that%20have%20a%20negative%20value.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1818738%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1818786%22%20slang%3D%22en-US%22%3ERe%3A%20SUMPRODUCT%20Function%20with%20Criteria%20for%20Visible%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1818786%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F846093%22%20target%3D%22_blank%22%3E%40cshanahan55%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20you%20go%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT((Table1%5BChange%5D%26lt%3B0)*SUBTOTAL(102%2COFFSET(E4%2CROW(Table1%5BChange%5D)-MIN(ROW(Table1%5BChange%5D))%2C0)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 Replies
Highlighted
Best Response confirmed by cshanahan55 (New Contributor)
Solution

@cshanahan55 

Here you go:

 

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

@Hans Vogelaar 

Awesome formula and solution.  Thanks!