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.  




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
Best Response confirmed by brentonhobden (New Contributor)


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



Thank you VERY much!

Works a treat.


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