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


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.