Sep 02 2019 10:19 PM
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.
Sep 02 2019 10:47 PM
SolutionYou 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)
Sep 02 2019 10:47 PM
SolutionYou 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)