Help Using a countif with multiple criteria but not counting filtered rows

%3CLINGO-SUB%20id%3D%22lingo-sub-1738635%22%20slang%3D%22en-US%22%3EHelp%20Using%20a%20countif%20with%20multiple%20criteria%20but%20not%20counting%20filtered%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1738635%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20this%20formula%3A%3C%2FP%3E%3CP%3E%3DCOUNTIF(P18%3AP66%2C%22C%20-%20ROR%22)%2BCOUNTIF(P18%3AP66%2C%22C%20-%20Letter%22)%2BCOUNTIF(P18%3AP66%2C%22C%20-%20Letter%20%26amp%3B%20Verbal%22)%2BCOUNTIF(P18%3AP66%2C%22C%20-%20NA%20for%20claimants%20%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20filter%20or%20hide%20rows%20I%20do%20not%20want%20to%20count%20it.%26nbsp%3B%20I%20am%20at%20a%20loss%20of%20what%20to%20do.%20What%20formula%20would%20you%20use%20to%20not%20count%20the%20hidden%20or%20sorted%20data%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1738635%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1738811%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20Using%20a%20countif%20with%20multiple%20criteria%20but%20not%20counting%20filtered%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1738811%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F817581%22%20target%3D%22_blank%22%3E%40Kboswell73%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%20this%20formula%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUM(SUMPRODUCT((P18%3AP66%3D%7B%22C%20-%20ROR%22%2C%22C%20-%20Letter%22%2C%22C%20-%20Letter%20%26amp%3B%20Verbal%22%2C%22C%20-%20NA%20for%20claimants%22%7D)*(SUBTOTAL(103%2COFFSET(P18%2CROW(P18%3AP66)-ROW(P18)%2C0%2C1)))))%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%20for%20background%20on%20this%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am using this formula:

=COUNTIF(P18:P66,"C - ROR")+COUNTIF(P18:P66,"C - Letter")+COUNTIF(P18:P66,"C - Letter & Verbal")+COUNTIF(P18:P66,"C - NA for claimants ")

 

If I filter or hide rows I do not want to count it.  I am at a loss of what to do. What formula would you use to not count the hidden or sorted data?

 

2 Replies

@Kboswell73 

 

Use this formula:

 

=SUM(SUMPRODUCT((P18:P66={"C - ROR","C - Letter","C - Letter & Verbal","C - NA for claimants"})*(SUBTOTAL(103,OFFSET(P18,ROW(P18:P66)-ROW(P18),0,1)))))

 

See Count visible rows only with criteria for background on this formula.