SOLVED

COUNTIF help

Copper 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
best response confirmed by brentonhobden (Copper Contributor)
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.

1 best response

Accepted Solutions
best response confirmed by brentonhobden (Copper Contributor)
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)

View solution in original post