SOLVED
Home

COUNTIF help

%3CLINGO-SUB%20id%3D%22lingo-sub-833990%22%20slang%3D%22en-US%22%3ECOUNTIF%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-833990%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3CBR%20%2F%3EI'm%20running%20a%20COUNT%20IF%20in%20a%20table%2C%20receiving%20data%20input%20from%20Forms.%26nbsp%3B%20The%20info%20is%20and%20will%20continue%20to%20come%20in%20for%20a%20long%20time%20yet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20I'm%20using%20is%20%3DCOUNTIF(%24D%242%3AD2%2CD2)%3C%2FP%3E%3CP%3EAs%20i%20progress%20to%20row%2015%2C%20it%20reads%20%3DCOUNTIF(%24D%242%3AD15%2CD15)%3C%2FP%3E%3CP%3EThis%20is%20so%20that%20I%20have%20a%20progressive%20number%20generator%20so%20that%20I%20can%20individualize%20every%20entry.%26nbsp%3B%20This%20serves%20to%20feed%20the%20table%20information%20into%20a%20tidier%20front%20end.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%20is%20that%20with%20each%20new%20entry%2C%20it%20interprets%20the%20count%20range%20to%20be%20(top%20of%20table%3Abottom%20of%20table)%3C%2FP%3E%3CP%3EI'd%20like%20to%20be%20able%20to%20have%20a%20progressive%20count%20function%2C%20that%20takes%20the%20row%20value%20as%20being%20the%20same%20as%20where%20the%20formula%20exists.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20668px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F129736iDDD6496EE1FC4142%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Snip.png%22%20title%3D%22Snip.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20above%2C%20col%20B%20has%20the%20formula%2C%20D%20is%20the%20code%20being%20used%2C%20E%20is%20a%20combination%20of%2C%20allowing%20for%20individual%20row%20lookup%20on%20the%20front%20end.%3C%2FP%3E%3CP%3EAs%20entry%20110%20comes%20in%20to%20row%20110%2C%20it%20will%20change%20the%20formula%20in%20109%20from%26nbsp%3B%3CSPAN%3E%3DCOUNTIF(%24D%242%3AD109%2CD109)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eto%26nbsp%3B%3CSPAN%3E%3DCOUNTIF(%24D%242%3AD110%2CD109).%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThat%20creates%20headaches%20where%20the%20value%20of%20D%20repeats%2C%20as%20it%20creates%20a%20count%20of%20the%20entire%20table%2C%20I%20want%20a%20count%20from%20table%20top%2C%20to%20current%20row%20value%20only.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIdeally%20I%20want%20this%20to%20all%20happen%20automatically%20without%20having%20to%20refresh%20the%20formulas.%26nbsp%3B%20It%20also%20needs%20to%20work%20in%20Excel%20online%20with%20its%20reduced%20functions%20(no%20Macros)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20hope%20all%20of%20that%20makes%20sense%20-%20any%20help%20appreciated.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-833990%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECOUNTIF%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-834014%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-834014%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402359%22%20target%3D%22_blank%22%3E%40brentonhobden%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20replace%20your%20formula%20with%20the%20following%20one%20to%20make%20it%20work%20with%20the%20self%20expandable%20range%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DCOUNTIF(D%242%3AINDIRECT(%22D%22%26amp%3BROW())%2CD2)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-834026%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-834026%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20VERY%20much!%3C%2FP%3E%3CP%3EWorks%20a%20treat.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-834028%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-834028%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402359%22%20target%3D%22_blank%22%3E%40brentonhobden%3C%2FA%3E!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3C%2FLINGO-BODY%3E
brentonhobden
New Contributor

Hi all,
I'm running a COUNT IF in a table, receiving data input from Forms.  The info is and will continue to come in for a long time yet.

 

The formula I'm using is =COUNTIF($D$2:D2,D2)

As i progress to row 15, it reads =COUNTIF($D$2:D15,D15)

This is so that I have a progressive number generator so that I can individualize every entry.  This serves to feed the table information into a tidier front end.

 

My problem is that with each new entry, it interprets the count range to be (top of table:bottom of table)

I'd like to be able to have a progressive count function, that takes the row value as being the same as where the formula exists.  

 

Snip.png

 

In the above, col B has the formula, D is the code being used, E is a combination of, allowing for individual row lookup on the front end.

As entry 110 comes in to row 110, it will change the formula in 109 from =COUNTIF($D$2:D109,D109)

 

to =COUNTIF($D$2:D110,D109).

 

That creates headaches where the value of D repeats, as it creates a count of the entire table, I want a count from table top, to current row value only.

 

Ideally I want this to all happen automatically without having to refresh the formulas.  It also needs to work in Excel online with its reduced functions (no Macros)

 

I hope all of that makes sense - any help appreciated.

3 Replies
Solution

@brentonhobden 

You may replace your formula with the following one to make it work with the self expandable range reference.

 

=COUNTIF(D$2:INDIRECT("D"&ROW()),D2)

Thank you VERY much!

Works a treat.

@Subodh_Tiwari_sktneer 

You're welcome @brentonhobden! Glad it worked as desired.

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies