Formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-2610741%22%20slang%3D%22en-US%22%3EFormula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2610741%22%20slang%3D%22en-US%22%3EI%20am%20needing%20a%20formula%20that%20goes%20to%20another%20sheet%20in%20the%20work%20book.%20It%20needs%20to%20check%20two%20Columns%20and%20meet%20certain%20criteria.%20If%20the%20two%20criteria%20are%20met%20it%20counts%201%20unit.%20I%20had%20this%20set%20up%20as%20a%20count%20ifs%20function%20and%20it%20works%2C%20but%20I%20would%20like%20it%20to%20ignore%20hidden%20%2F%20filtered%20cells.%20I%20have%20tried%20sumproduct(subtotal%20etc%E2%80%A6%20but%20can%20not%20get%20that%20to%20not%20count%20the%20hidden%20cells.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2610741%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2610844%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2610844%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1120419%22%20target%3D%22_blank%22%3E%40Coltenk54%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20don't%20provide%20any%20details%2C%20so%20I%20can%20only%20provide%20an%20example%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT(('Other%20Sheet'!B2%3AB100%3D37)*('Other%20Sheet'!C2%3AC100%3D%22Yes%22)*SUBTOTAL(103%2COFFSET('Other%20Sheet'!B2%2CROW('Other%20Sheet'!B2%3AB100)-MIN(ROW('Other%20Sheet'!B2%3AB100))%2C0%2C1)))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20counts%20the%20number%20of%20visible%20rows%20on%20Other%20Sheet%20where%20B2%3AB100%20equals%2037%20and%20C2%3AC100%20equals%20Yes.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fcount-visible-rows-only-with-criteria%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3ECount%20visible%20rows%20only%20with%20criteria%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
I am needing a formula that goes to another sheet in the work book. It needs to check two Columns and meet certain criteria. If the two criteria are met it counts 1 unit. I had this set up as a count ifs function and it works, but I would like it to ignore hidden / filtered cells. I have tried sumproduct(subtotal etc… but can not get that to not count the hidden cells.
2 Replies

@Coltenk54 

You don't provide any details, so I can only provide an example:

 

=SUMPRODUCT(('Other Sheet'!B2:B100=37)*('Other Sheet'!C2:C100="Yes")*SUBTOTAL(103,OFFSET('Other Sheet'!B2,ROW('Other Sheet'!B2:B100)-MIN(ROW('Other Sheet'!B2:B100)),0,1)))

 

This counts the number of visible rows on Other Sheet where B2:B100 equals 37 and C2:C100 equals Yes.

 

See Count visible rows only with criteria 

 

 

Thank you so much!