SOLVED
Home

COUNTIF WITH OFFSET

%3CLINGO-SUB%20id%3D%22lingo-sub-532929%22%20slang%3D%22en-US%22%3ECOUNTIF%20WITH%20OFFSET%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-532929%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20a%20the%20following%20formula%20in%20row%20I57%2C%20COUNTIF(I6%3AI56%2C%22%26lt%3B%26gt%3B0)%2C%20which%20works%20fine%2C%20but%20that%20column%20will%20eventually%20have%20more%20data%20and%20I%20want%20to%20make%20it%20dynamic%20so%20I%20decided%20to%20place%20the%20new%20formula%20to%20row%20I4%20which%20is%20one%20row%20above%20the%20header%20(in%20I5).%20So%20anything%20from%20I6%20and%20downwards%20I%20need%20to%20do%20the%20count.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESo%20I%20tired%20in%20I4%20%3A%20%3DCOUNTIF(OFFSET(%24I%245%2C1%2C%2CCOUNTA(%24I%3A%24I)%2C1)%2C%22%26lt%3B%26gt%3B0%22)%20-%20not%20getting%20the%20result%20and%20even%20experimented%20with%20%3DCOUNTIF(%24I%246%3AINDEX(I%3AI%2CI1)%2C%22%26lt%3B%26gt%3B0%22).%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EAny%20ideas%3F%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThanks%2C%3C%2FP%3E%3CP%3EJay.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-532929%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-533121%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20WITH%20OFFSET%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-533121%22%20slang%3D%22en-US%22%3EIf%20I1%3AI3%20are%20blank%2C%20you%20may%20define%20CountRange%2C%20while%20I4%20is%20selected%2C%20with%20this%20formula%3A%3CBR%20%2F%3E%3DI6%3AINDEX(I6%3AI1048576%2CCOUNTA(I6%3AI1048576))%3CBR%20%2F%3EThereafter%2C%20you%20may%20then%20enter%20this%20formula%20in%20I4%3A%3CBR%20%2F%3E%3DCOUNTIF(CountRange%2C%22%26lt%3B%26gt%3B0%22)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-538568%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20WITH%20OFFSET%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-538568%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-538905%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20WITH%20OFFSET%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-538905%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20welcome.%3C%2FLINGO-BODY%3E
AVP68
Occasional Contributor

Hello,

I have a the following formula in row I57, COUNTIF(I6:I56,"<>0), which works fine, but that column will eventually have more data and I want to make it dynamic so I decided to place the new formula to row I4 which is one row above the header (in I5). So anything from I6 and downwards I need to do the count.


So I tired in I4 : =COUNTIF(OFFSET($I$5,1,,COUNTA($I:$I),1),"<>0") - not getting the result and even experimented with =COUNTIF($I$6:INDEX(I:I,I1),"<>0").


Any ideas?


Thanks,

Jay.

3 Replies
Solution
If I1:I3 are blank, you may define CountRange, while I4 is selected, with this formula:
=I6:INDEX(I6:I1048576,COUNTA(I6:I1048576))
Thereafter, you may then enter this formula in I4:
=COUNTIF(CountRange,"<>0")

@Twifoo  Thanks!

You’re welcome.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
11 Replies